<?php

namespace App\Libs\wrapper;

use App\Models\ShopeeOrderItems;
use Illuminate\Support\Facades\DB;
use phpDocumentor\Reflection\DocBlock\Tags\Var_;


class Shopee extends Comm
{

    public $status = "('COMPLETED','SHIPPED','TO_CONFIRM_RECEIVE','READY_TO_SHIP')";


    /**
     * shopee流量转化表 --数据
     * shopee_link shopee商品链接数据表
     * email_reminder 邮件提醒表
     */
    public function shopee_flow_conversion($data)
    {

        $where = "1=1";
        //shop_id 店铺id

        if (!isset($data['shop_id'])) {
            $where_store = '';
            if($data['shopee_list_all']===false){
                $store_user = json_encode([$data['user_info']['Id']]);
                $where_store = " and store_user='{$store_user}'";
            }

            $shop_sql = "select Id,shop_name from shop where platform_id=7 and state=1".$where_store;
            $shop = json_decode(json_encode(db::select($shop_sql)), true);
            if ($shop) {
                $data['shop_id'] = $shop[0]['Id'];

            }

        }
        /*if (isset($data['shop_id'])) {
            $where .= " and `shop_id` = {$data['shop_id']}";
        }*/
        $shop_name = Db::table('shop')->where('Id', $data['shop_id'])->pluck('shop_name');

        if (!isset($data['start_date'])) {
            $data['start_date'] = date('Y-m-d', strtotime('last Friday'));
            $data['end_date'] = date('Y-m-d', strtotime('Thursday'));

        }
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
//        var_dump($start_time.'-'.$end_time);die;
        $weekarray = array("日", "一", "二", "三", "四", "五", "六");
        if ($weekarray[date("w", strtotime($start_date))] != '五' ||
            $weekarray[date("w", strtotime($end_date))] != '四') {
            $a = "请选择起始日为周五,且结束日为周四的区间, 当前选择起始日为周{$weekarray[date("w", strtotime($start_date))]},结束日为周{$weekarray[date("w", strtotime($end_date))]}";
            return $a;

        }
//        $date = $data['date'];
        $where .= " and sl.shop_id = {$data['shop_id']} and sl.start_date= '{$start_date}' and sl.end_date='{$end_date}'";


        //查找shopee_link表数据
        $sql = "select
	                sl.id,sl.goods_name,sl.goods_id,sl.goods_view,sl.pieces_confirmed_order,sl.access_confirm_rate,sl.confirmed_order_sales_us,sl.shopcart_quantity,
	                sl.shopcart_rate,sl.goods_bounce_rate,sl.date, 0.00 as customer_unit_price,0 as is_low_access,0 as is_low_shopcart,0 as is_low_bounce,s.create_user,
	                sl.shop_id,s.shop_name,sct.access_confirm_task
	              from shopee_link sl
	              left join shop s on s.Id=sl.shop_id
	              left join shopee_conversion_task sct on sct.goods_id=sl.goods_id
	              where {$where}
	              order by goods_view desc
	              limit 10";

        $list = json_decode(json_encode(db::select($sql)), true);

        //// 总条数
        if (isset($data['page']) and isset($data['page_count'])) {
            $total = db::select("SELECT FOUND_ROWS() as total");
            $return['total'] = $total[0]->total;
        }


        foreach ($list as $key => $value) {
            if ($value['pieces_confirmed_order'] > 0) {
                $list[$key]['customer_unit_price'] = round($value['confirmed_order_sales_us'] / $value['pieces_confirmed_order'], 2);
            }
            if ($value['access_confirm_task'] > 0) {
                if ((float)$value['access_confirm_rate'] / 100 < $value['access_confirm_task'] / 100) {
                    $list[$key]['is_low_access'] = 1;
//                    $access_content .= "链接转化率为{$value['access_confirm_rate']},低于3.1%";

                }
            }
            if ((float)$value['shopcart_rate'] / 100 < (float)('12' / 100)) {
                $list[$key]['is_low_shopcart'] = 1;
//                    $cart_content .= "  加购率为{$value['shopcart_rate']},低于12%";
//                    var_dump($value['is_low_shopcart']);
            }
            if ((float)$value['goods_bounce_rate'] / 100 > (float)('50' / 100)) {
                $list[$key]['is_low_bounce'] = 1;
//                    $bounce_content .= "  跳出率为{$value['goods_bounce_rate']},高于50%";
            }


        }
//            var_dump($list);

        $return['list'] = $list;
        $return['shop_name'] = $shop_name[0];


        return $return;


    }


    /**
     * 修改shopee流量转化的转化率指标
     * @table shopee_conversion_task
     * @param access_confirm_task 转化率指标
     * @param goods_id 商品编号
     * @param type 报表类型：1.shopee流量转化表；
     */
    public function shopee_flow_update($data)
    {
        $access_confirm_task = $data['access_confirm_task'];
        $goods_id = $data['goods_id'];
        $task = Db::table('shopee_conversion_task')->where('goods_id', $goods_id)->where('type', 1)->pluck('id');
//        var_dump($task[0]);die;
        if (!isset($task[0])) {
            $insert = Db::table('shopee_conversion_task')->insert(['access_confirm_task' => $access_confirm_task, 'goods_id' => $goods_id, 'type' => 1]);
            if ($insert) {
                return 1;
            }
        }
        return '编辑失败';


    }


    /**
     * 店铺资产表
     * @table  shopee_order         shopee订单列表
     *         shopee_order_items   shopee订单详情表
     *         shopee_cost          shopee成本表
     *         shopee_stock         shopee库存表
     *         shop                 店铺表
     * @param $data
     * @return string
     */
    public function shopee_shop_assets($data)
    {


        $where = "1=1";
        //shop_id 店铺id
        if (!isset($data['shop_id']) && !isset($data['user_id'])&&!isset($data['country_id'])) {
            $where_store = '';
            if($data['shopee_list_all']===false){
                $store_user = json_encode([$data['user_info']['Id']]);
                $where_store = " and store_user='{$store_user}'";
            }
            $shop_sql = "select Id,shop_name from shop where platform_id=7 and state=1".$where_store;
            $shop = json_decode(json_encode(db::select($shop_sql)), true);
            if (!$shop) {
                return '没有shopee店铺';
            }
            $shop_id = array_column($shop, 'Id');
            $data['shop_id'] = explode(',', $shop_id[0]);
        }

        if (isset($data['user_id'])) {
            $data['user_id'] = json_encode([$data['user_id']]);
            $where .= " and store_user = '{$data['user_id']}'";
        }
//        var_dump($where);die;
        if (isset($data['shop_id'])) {
            $data['shop_id'] = implode(',', $data['shop_id']);
            $where .= " and Id in ({$data['shop_id']})";
        }
        if (isset($data['country_id'])) {
            $data['country_id'] = json_encode([$data['country_id']]);
            $where .= " and country_id in ({$data['country_id']})";
        }


        $shop_data = Db::table('shop')->select('Id', 'shop_name')->whereRaw($where)->get()->toArray();
//        var_dump($shop_data);die;
//        $data['date'] = '2022-02-15';

        if (!isset($data['start_date'])) {
            $data['start_date'] = date('Y-m-d', strtotime('last Friday'));
            $data['end_date'] = date('Y-m-d', strtotime('Thursday'));
            /*$date_now = date('Y-m', time());
            $day = date('d', time());
            //判断每周的第一天是几号
            if ($day >= 1 && $day <= 7) {
                $date_now .= '-01';
            }
            if ($day >= 8 && $day <= 14) {
                $date_now .= '-08';
            }
            if ($day >= 15 && $day <= 21) {
                $date_now .= '-15';
            }
            if ($day >= 22 && $day <= 31) {
                $date_now .= '-22';
            }
            $data['date'] = $date_now;*/
        }

        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
//        var_dump($start_time.'-'.$end_time);die;
        $weekarray = array("日", "一", "二", "三", "四", "五", "六");
        if ($weekarray[date("w", strtotime($start_date))] != '五' ||
            $weekarray[date("w", strtotime($end_date))] != '四') {
            $a = "请选择起始日为周五,且结束日为周四的区间, 当前选择起始日为周{$weekarray[date("w", strtotime($start_date))]},结束日为周{$weekarray[date("w", strtotime($end_date))]}";
            return $a;

        }

        //本期时间
//        $date = $data['date'];
        $start_time = $start_date . ' 00:00:00';
        $end_time = $end_date . ' 23:59:59';

//        var_dump($start_time);die;
        foreach ($shop_data as $shop_key => $shop_value) {
//            var_dump($shop_value);die;
            $shop_name = $shop_value->shop_name;
            $shop_id = $shop_value->Id;


            /*******本期*******/

            $name = '本周';
            $assets_data[0] = $this->assets_data($start_date, $end_date, $start_time, $end_time, $shop_id, $shop_name, $name);
            $assets_data[0]['shop_id'] = $shop_id;


            /*******上期*******/
            //上期时间
            $last_start_date = date('Y-m-d', strtotime('-7 days', strtotime($start_date)));
            $last_end = date('Y-m-d', strtotime('-1 days', strtotime($start_date)));
            $last_end_date = $start_time . ' 00:00:00';
            $last_start_time = $last_start_date . ' 00:00:00';
            $name = '上周';
            $last_shop_name = '';
            $assets_data[1] = $this->assets_data($last_start_date, $last_end, $last_start_time, $last_end_date, $shop_id, $last_shop_name, $name);
            $assets_data[1]['shop_id'] = $shop_id;

            $assets_data[1]['circulation_rate'] = $assets_data[1]['variation_quantity_purchased'] == 0 ? 0 : round(($assets_data[0]['variation_quantity_purchased'] - $assets_data[1]['variation_quantity_purchased']) / $assets_data[1]['variation_quantity_purchased'] * 100, 2) . '%';//环量增长比
            $assets_data[1]['circulation_discounted_rate'] = $assets_data[1]['variation_discounted_price'] == 0 ? 0 : round(($assets_data[0]['variation_discounted_price'] - $assets_data[1]['variation_discounted_price']) / $assets_data[1]['variation_discounted_price'] * 100, 2) . '%';//销售额环比增长比
            $assets_data[1]['circulation_gross_rate'] = $assets_data[1]['gross_profit'] == 0 ? 0 : round(($assets_data[0]['gross_profit'] - $assets_data[1]['gross_profit']) / $assets_data[1]['gross_profit'] * 100, 2) . '%';//毛利环比增长比

            $assets_data[0]['circulation_rate'] = $assets_data[1]['variation_quantity_purchased'] == 0 ? 0 : round(($assets_data[0]['variation_quantity_purchased'] - $assets_data[1]['variation_quantity_purchased']) / $assets_data[1]['variation_quantity_purchased'] * 100, 2) . '%';//环量增长比
            $assets_data[0]['circulation_discounted_rate'] = $assets_data[1]['variation_discounted_price'] == 0 ? 0 : round(($assets_data[0]['variation_discounted_price'] - $assets_data[1]['variation_discounted_price']) / $assets_data[1]['variation_discounted_price'] * 100, 2) . '%';//销售额环比增长比
            $assets_data[0]['circulation_gross_rate'] = $assets_data[1]['gross_profit'] == 0 ? 0 : round(($assets_data[0]['gross_profit'] - $assets_data[1]['gross_profit']) / $assets_data[1]['gross_profit'] * 100, 2) . '%';//毛利环比增长比

            //本期

            $receipt = $this->assets_balance($start_date, $end_date, $shop_id);


            //上期

            $last_receipt = $this->assets_balance($last_start_date, $last_end, $shop_id);


            //上期//本期余额
            $vacancies['current_balance'] = isset($receipt['balance']->current_balance_us) ? $receipt['balance']->current_balance_us : '0.00';
            //回款金额
            $vacancies['collection_amount'] = isset($receipt['balance']->collection_amount_us) ? $receipt['balance']->collection_amount_us : '0.00';
            //待回款
            $vacancies['pending_payment'] = isset($receipt['balance']->pending_payment) ? $receipt['balance']->pending_payment : '0.00';
            //库存价值
            $vacancies['ending_inventory_price'] = empty($receipt['ending_inventory_price']) ? '0.00' : $receipt['ending_inventory_price'];
            //上期余额
            $vacancies['last_current_balance'] = isset($last_receipt['balance']->current_balance_us) ? $last_receipt['balance']->current_balance_us : '0.00';
            //上期回款
            $vacancies['last_collection_amount'] = isset($last_receipt['balance']->collection_amount_us) ? $last_receipt['balance']->collection_amount_us : '0.00';
            //上期库存
            $vacancies['last_ending_inventory_price'] = empty($last_receipt['last_ending_inventory_price']) ? '0.00' : $last_receipt['last_ending_inventory_price'];
            //库存增量
            $vacancies['inventory_increment'] = round(bcsub($vacancies['ending_inventory_price'], $vacancies['last_ending_inventory_price']), 2);
            //资产增量
            $vacancies['asset_increment'] = bcadd(bcadd(bcsub($vacancies['current_balance'], $vacancies['last_current_balance']), $vacancies['collection_amount']), $vacancies['inventory_increment']);
            //店铺名
            $vacancies['shop_name'] = $shop_name;
            $vacancies['shop_id'] = $shop_id;
            //本周的数据集合
            $assets_data_arr[0][] = $assets_data[0];
            //上周的数据集合
            $assets_data_arr[1][] = $assets_data[1];
            $vacancies_data[] = $vacancies;
        }


        if(!isset($assets_data_arr)){
            $assets_data_arr[0] = [];
            $assets_data_arr[1] = [];
        }
        if(!isset($vacancies_data)){
            $vacancies_data = [];
        }
//        var_dump($assets_data_arr[0]);die;
        //给本周的数据按照毛利gross_profit从大到小排序
        $gross_profit = [];
        foreach ($assets_data_arr[0] as $asset_value) {
            $gross_profit[] = $asset_value['gross_profit'];
        }
        array_multisort($gross_profit, SORT_DESC, $assets_data_arr[0]);

        //将本周和上周的数据按照本周上周为一组插入到新数组$assets_list中成为二维数组
        $assets_list = [];
        foreach ($assets_data_arr[0] as $ke => $va) {
            //将遍历出来的本周数据插入数组
            array_push($assets_list, $va);
            //遍历上周数据
            foreach ($assets_data_arr[1] as $assets_ke => $assets_va) {
                //判断如果本周数据中的shop_id等于上周数据中的shop_id，将上周的数据插入到本周数据的下面
                if ($assets_va['shop_id'] == $va['shop_id']) {
                    array_push($assets_list, $assets_va);
                }
            }

        }


        $return['list'] = $assets_list;
        $return['balance'] = $vacancies_data;
//        $return['shop_name'] = $shop_name[0];
//        $return['month'] = $date_month . '月' . $week;


        return $return;

    }

    //店铺资产销售额封装
    public function assets_data($start_date, $end_date, $start_time, $end_time, $shop_id, $shop_name, $name)
    {
        //查找shopee_link表数据
        //货币代码，平台成本=service_fee_us+voucher_seller_us+credit_card_transaction_fee_us+commission_fee_us
        $sql = "select so.currency,so.service_fee_us,so.voucher_seller_us,so.credit_card_transaction_fee_us,so.commission_fee_us,so.ordersn,so.create_time
                    from shopee_order so 
                    left join shop s on s.Id=so.shop_id
                    where so.order_status !='CANCELLED'
                    and (so.create_time between '{$start_time}' and '{$end_time}') and so.shop_id={$shop_id}
                 ";


        $list = json_decode(json_encode(db::select($sql)), true);

//            var_dump($list);die;

        $order_sn = array_column($list, 'ordersn');
//        var_dump($order_sn);die;
        $assets_data = [];
        if (!empty($shop_name)) {
            $assets_data['shop_name'] = $shop_name;
        }


        //销售额
        $total_price = Db::table('shopee_order')
            ->whereIn('ordersn', $order_sn)
            ->sum('variation_total_price_us');

        $item_list = Db::table('shopee_order_items as soi')
            ->select('soi.variation_sku', 'sc.cost_us', 'soi.item_id', 'sc.comprehensive_cost_us', 'soi.variation_discounted_price_us', 'soi.variation_quantity_purchased', 'soi.item_sku')
            ->leftJoin('shopee_cost as sc', 'sc.product_sku', '=', 'soi.variation_sku')
            ->whereIn('soi.ordersn', $order_sn)
            ->get()
            ->toArray();
        foreach ($item_list as $item_k => $item_v) {
            $item_list[$item_k]->cost_total = $item_v->cost_us * $item_v->variation_quantity_purchased;
        }
        //var_dump(array_sum(array_column($item_list, 'cost_us')));//die;
//            var_dump($item_list);die;
        $sku = Db::table('shopee_order_items')
            ->select('variation_sku')
            ->whereIn('ordersn', $order_sn)
            ->groupBy('variation_sku')
            ->get()
            ->toArray();
        $sku_arr = array_column($sku, 'variation_sku');
        /*$cost_data = Db::table('shopee_cost')
            ->select('comprehensive_cost_us', 'cost_us')
            ->whereIn('product_sku', $sku_arr)
            ->get()
            ->toArray();*/
        /*$cost_data = Db::table('shopee_order_items as soi')
            ->leftJoin('shopee_order as so', 'so.ordersn', '=', 'soi.ordersn')
            ->leftJoin('shopee_cost as sc', 'sc.product_sku', '=', 'soi.variation_sku')
            ->where('so.shop_id', $shop_id)
            ->whereBetween('so.create_time', [$start_time, $end_time])
            ->sum('sc.comprehensive_cost_us');*/
        $cost_data = Db::table('shopee_order_items as soi')
            ->select('sc.comprehensive_cost_us')
            ->leftJoin('shopee_order as so', 'so.ordersn', '=', 'soi.ordersn')
            ->leftJoin('shopee_cost as sc', 'sc.product_sku', '=', 'soi.variation_sku')
            ->where('so.shop_id', $shop_id)
            ->whereBetween('so.create_time', [$start_time, $end_time])
            ->first();

        // var_dump($cost_data->comprehensive_cost_us);die;
        $order_count = count($list);
//            $cost = round(array_sum(array_column($item_list, 'cost_us')), 2);//采购成本
//            $cost = round(array_sum(array_column($cost_data, 'cost_us')), 2);//采购成本
        $cost = round(array_sum(array_column($item_list, 'cost_total')), 2);//采购成本
//            $variation_discounted_price = round(array_sum(array_column($item_list, 'variation_discounted_price_us')), 2);//销售额
        $variation_discounted_price = round($total_price, 2);//销售额
        //$comprehensive_cost = round(array_sum(array_column($cost_data, 'comprehensive_cost_us')), 2);//综合成本
        //$comprehensive_cost = $cost_data;//综合成本
        $comprehensive_cost = !empty($cost_data) ? bcmul($cost_data->comprehensive_cost_us, $order_count, 2) : 0;//综合成本
//            $comprehensive_cost = round(array_sum(array_column($item_list, 'comprehensive_cost_us')), 2);//综合成本
        $variation_quantity_purchased = round(array_sum(array_column($item_list, 'variation_quantity_purchased')), 2);//销售量
        $item_id = array_filter(array_column($item_list, 'item_id'));
        $add_list = Db::table('shopee_adv')->select('cost_us')->where(['start_date' => $start_date, 'end_date' => $end_date, 'shop_id' => $shop_id])->get()->toArray();
//            var_dump($add_list);die;
        $adv_cost = round(array_sum(array_column($add_list, 'cost_us')), 2);//广告费
        $platform_cost = round(array_sum(array_column($list, 'service_fee_us')), 2) + round(array_sum(array_column($list, 'voucher_seller_us')), 2) + round(array_sum(array_column($list, 'credit_card_transaction_fee_us')), 2) + round(array_sum(array_column($list, 'commission_fee_us')), 2);//平台成本
//        $platform_cost = round(array_sum(array_column($list, 'service_fee'))+array_sum(array_column($list, 'voucher_seller_us'))+array_sum(array_column($list, 'credit_card_transaction_fee_us')),2);//平台成本


        $assets_data['name'] = $name;
        $assets_data['variation_discounted_price'] = $variation_discounted_price;//销售额
        $assets_data['comprehensive_cost'] = $comprehensive_cost;//综合成本
        $assets_data['variation_quantity_purchased'] = $variation_quantity_purchased;//销售量
        $assets_data['adv_cost'] = $adv_cost;//广告费
        $assets_data['cost'] = $cost;//采购成本
        $assets_data['platform_cost'] = round($platform_cost, 2);//平台成本
        $assets_data['gross_profit'] = round($variation_discounted_price - $platform_cost - $cost - $comprehensive_cost - $adv_cost, 2);//毛利
        $assets_data['platform_rate'] = $variation_discounted_price == 0 ? 0 : round($platform_cost / $variation_discounted_price * 100, 2) . '%';//平台成本占比
        $assets_data['cost_rate'] = $variation_discounted_price == 0 ? 0 : round($cost / $variation_discounted_price * 100, 2) . '%';//采购成本占比
        $assets_data['comprehensive_rate'] = $variation_discounted_price == 0 ? 0 : round($comprehensive_cost / $variation_discounted_price * 100, 2) . '%';//综合费用占比
        $assets_data['adv_rate'] = $variation_discounted_price == 0 ? 0 : round($adv_cost / $variation_discounted_price * 100, 2) . '%';//广告占比
        $assets_data['gross_rate'] = $variation_discounted_price == 0 ? 0 : round($assets_data['gross_profit'] / $variation_discounted_price * 100, 2) . '%';//毛利占比
        if (trim($assets_data['gross_rate'],'%')  * 100 < 15) {
            $assets_data['is_remind'] = 1;
        } elseif (trim($assets_data['gross_rate'],'%') * 100 == 15) {
            $assets_data['is_remind'] = 2;
        }

        return $assets_data;
    }

    //店铺资产余额封装
    public function assets_balance($start_date, $end_date, $shop_id)
    {
        $balance = Db::table('shopee_balance')->where('start_date', $start_date)->where('end_date', $end_date)->where('shop_id', $shop_id)->first();
        //本期库存值
        $ending_inventory_price = Db::table('shopee_stock')
            ->where('shop_id', $shop_id)
            ->where('start_date', $start_date)
            ->where('end_date', $end_date)
            ->sum('ending_inventory_price_us');

        $return['balance'] = $balance;
        $return['ending_inventory_price'] = $ending_inventory_price;
        return $return;
    }

    /**
     * shopee店铺资产编辑
     */

    public function shopee_assets_update($data)
    {
        $shop_id = $data['shop_id'];
        /*      $currency = Db::table('shop as s')
                  ->select('c.currency')
                  ->leftJoin('countrys as c', 'c.Id', 's.country_id')
                  ->where('s.Id', $shop_id)
                  ->get()
                  ->toArray();*/
        $shop_data = Db::table('shop')
            ->select('country_id')
            ->where('Id', $shop_id)
            ->get()
            ->toArray();
        foreach ($shop_data as $key=>$value){
            $country_id = json_decode($value->country_id);
            $currency_str = Db::table('countrys')->select('currency')->where('Id',$country_id)->get()->toArray();
            $currency[] = $currency_str[0];
        }
//        var_dump($currency[0]);die;


//        var_dump($currency);die;
        if (!$currency[0]->currency) {
            return '未找到店铺货币代码';
        }

        $currency_rate = Db::table('huilv')->select('dollar_rate', 'id')->where('currency', $currency[0]->currency)->orderBy('add_time', 'desc')->limit(1)->get()->toArray();
        $dollar_rate = $currency_rate[0]->dollar_rate;
        if (isset($data['current_balance'])) {
            $param['current_balance'] = $data['current_balance'];//账号余额
            $param['current_balance_us'] = bcmul($data['current_balance'], $dollar_rate, 2);//账号余额
        } elseif (isset($data['collection_amount'])) {
            $param['collection_amount'] = $data['collection_amount'];//回款金额
            $param['collection_amount_us'] = bcmul($data['collection_amount'], $dollar_rate, 2);//回款金额
        } elseif (isset($data['pending_payment'])) {
            $param['pending_payment'] = $data['pending_payment'];//待回款
            $param['pending_payment_us'] = bcmul($data['pending_payment'], $dollar_rate, 2);//待回款
        } elseif (isset($data['inventory_value'])) {
            $param['inventory_value'] = $data['inventory_value'];//库存价值
            $param['inventory_value_us'] = bcmul($data['inventory_value'], $dollar_rate, 2);//库存价值
        }
        $param['shop_id'] = $shop_id;
        $param['huilv_id'] = $currency_rate[0]->id;
        //计算当前时间
        $now_start_date = date('Y-m-d', strtotime('last Friday'));
        $now_end_date = date('Y-m-d', strtotime('Thursday'));

        if (!isset($data['start_date'])) {
            $data['start_date'] = $now_start_date;
            $data['end_date'] = $now_end_date;

        }

        //搜索时间
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];


        if (isset($data['start_date']) && ($data['start_date'] != $now_start_date)) {
            return '只能修改本期数据';
        }

        $param['start_date'] = $start_date;
        $param['end_date'] = $end_date;

        $task = Db::table('shopee_balance')->where('shop_id', $shop_id)->where('start_date', $start_date)->where('end_date', $end_date)->pluck('id');
//        var_dump($task[0]);die;
        if (!isset($task[0])) {
            $res = Db::table('shopee_balance')->insert($param);

        } else {
            $res = Db::table('shopee_balance')->where('shop_id', $shop_id)->where('start_date', $start_date)->where('end_date', $end_date)->update($param);
        }
        if ($res) {
            return 1;
        }
        return '编辑失败';

    }


    public function shopeeStockList($data)
    {

        $where = '1=1';
        $limit = "";
        if ((!empty($data['page'])) and (!empty($data['limit']))) {
            $limit = " limit " . ($data['page'] - 1) * $data['limit'] . ",{$data['limit']}";
        }
        if (isset($data['start_date'])) {
            $start_date = $data['start_date'];
            $end_date = $data['end_date'];
            $weekarray = array("日", "一", "二", "三", "四", "五", "六");
            if ($weekarray[date("w", strtotime($start_date))] != '五' ||
                $weekarray[date("w", strtotime($end_date))] != '四') {
                $a = "请选择起始日为周五,且结束日为周四的区间, 当前选择起始日为周{$weekarray[date("w", strtotime($start_date))]},结束日为周{$weekarray[date("w", strtotime($end_date))]}";
                return $a;

            }

            $where .= " and start_date='{$start_date}' and end_date='{$end_date}'";

        }

        if (isset($data['shop_id'])) {
            $where .= " and shop_id='{$data['shop_id']}'";
        }

        if (isset($data['sku'])) {
            $where .= " and sku='{$data['sku']}'";
        }

        if (isset($data['country_id'])) {
            $data['country_id'] = json_encode([$data['country_id']]);
            $where .= " and s.country_id='{$data['country_id']}'";
        }

        /*   $sql = "select SQL_CALC_FOUND_ROWS sc.*, s.shop_name,(select c.name from countrys c where c.Id=s.country_id) as country_name
                       from shopee_stock sc join shop s on sc.shop_id = s.ID
                       where {$where}
                       {$limit}";*/
        $sql = "select SQL_CALC_FOUND_ROWS sc.*, s.shop_name,s.country_id
					from shopee_stock sc join shop s on sc.shop_id = s.ID
					where {$where}
					{$limit}";
//        var_dump($sql);die;
        $list = json_decode(json_encode(db::select($sql)), true);

        ////总条数
        $count = db::select("SELECT FOUND_ROWS() as total");

        foreach ($list as $key=>$value){
            $country_id = json_decode($value['country_id']);
            $country_name = Db::table('countrys')->where('Id',$country_id)->pluck('name');
            $list[$key]['country_name'] = $country_name;
        }


        $return['list'] = $list;
        $return['total'] = $count[0]->total;
        return $return;

    }


    /**
     * shopee退货表
     */
    public function shopee_return($data)
    {
        $where = "1=1";
        $return_list = [];
        //shop_id 店铺id
        if (!isset($data['shop_id'])) {
            $where_store = '';
            if($data['shopee_list_all']===false){
                $store_user = json_encode([$data['user_info']['Id']]);
                $where_store = " and store_user='{$store_user}'";
            }
            $shop_sql = "select Id,shop_name from shop where platform_id=7 and state=1".$where_store;
            $shop = json_decode(json_encode(db::select($shop_sql)), true);
            if (!$shop) {
                return '没有shopee店铺';
            }
            $data['shop_id'] = $shop[0]['Id'];

        }
        if (isset($data['shop_id'])) {
            $where .= " and `shop_id` = {$data['shop_id']}";
        }
        $shop_name = Db::table('shop')->where('Id', $data['shop_id'])->pluck('shop_name');

//        //$data['date'] = '2022-02-15';
//        if (!isset($data['date'])) {
//            $date_now = date('Y-m', time());
//            $day = date('d', time());
//            //判断每周的第一天是几号
//            if ($day >= 1 && $day <= 7) {
//                $date_now .= '-01';
//            }
//            if ($day >= 8 && $day <= 14) {
//                $date_now .= '-08';
//            }
//            if ($day >= 15 && $day <= 21) {
//                $date_now .= '-15';
//            }
//            if ($day >= 22 && $day <= 31) {
//                $date_now .= '-22';
//            }
//            $data['date'] = $date_now;
//        }
//
//        //本期时间
//        $data['date'] = '2022-02-15';
//        $date = $data['date'];
//
//        //判断是第几周
//        $date_day = date('d', strtotime($date));
//        $week = '';
//        if ($date_day == '01') {
//            $week = '第一周';
//        } elseif ($date_day == '08') {
//            $week = '第二周';
//        } elseif ($date_day == '15') {
//            $week = '第三周';
//        } elseif ($date_day == '22') {
//            $week = '第四周';
//        }
//        //判断是几月份
//        $date_month = date('m', strtotime($date));
//
//        $start_time = $date . ' 00:00:00';
//
//        if ($date_day > 22 || $date_day == 22) {
//            $end_date = date('Y-m-t', strtotime($date)) . ' 23:59:59';
//        } else {
//            $end_date = date('Y-m-d', strtotime('+7 days', strtotime($date))) . ' 00:00:00';
//        }


        if (!isset($data['start_date']) || !isset($data['end_date'])) {
            $data['start_date'] = date('Y-m-d');
            $data['end_date'] = date('Y-m-d', strtotime('- 7 days'));
        }

        $startDateTime = $data['start_date'] . ' 00:00:00';//起始时间 日期格式
        $endDateTime = $data['end_date'] . ' 23:59:59'; //截止时间 日期格式

        //判断是第几周
        $date_day = date('d', strtotime($startDateTime));
        $week = '';
        if ($date_day == '01') {
            $week = '第一周';
        } elseif ($date_day == '08') {
            $week = '第二周';
        } elseif ($date_day == '15') {
            $week = '第三周';
        } elseif ($date_day == '22') {
            $week = '第四周';
        }
        //判断是几月份
        $date_month = date('m', strtotime($startDateTime));

        $start_time = $startDateTime;
        $end_date = $endDateTime;

        //查询当前店铺当前时间退货的单号、原因
        $sql = "SELECT 
                       sr.ordersn,so.create_time,so.shop_id,sr.reason
                      from shopee_returns sr
                      left join shopee_order so on so.ordersn=sr.ordersn
                      where so.shop_id={$data['shop_id']}
                        and so.create_time between '{$start_time}' and '{$end_date}'
                     ";

        $list = json_decode(json_encode(db::select($sql)), true);
//        var_dump(empty($list));
        if (empty($list)) {
            $return['list'] = [];
            return $return;
        }
        $items = [];
        foreach ($list as $key => $value) {
            $order_sn = $value['ordersn'];
            //查询退货的商品编号，名称，每单购买数量
            $order_items = Db::table('shopee_order_items')->select('item_id', 'item_name', 'variation_quantity_purchased')->where('ordersn', $order_sn)->get()->toArray();
            foreach ($order_items as $item_key => $item_value) {
                $item_value->ordersn = $value['ordersn'];
                $item_value->reason = $value['reason'];
                array_push($items, $item_value);

            }
        }

        //将商品编号从数组中提取出来并取出重复值
        $item = array_unique(array_column($items, 'item_id'));
//        $reason = array_unique(array_column($items, 'reason'));
        foreach ($item as $k => $v) {
            $arr_item['item_id'] = $v;
            $array_item[] = $arr_item;
        }

        foreach ($array_item as $it_key => $it_value) {
            $reason_arr = [];
            $it_value['return_num'] = 0;//退货总数
            foreach ($items as $items_key => $items_value) {
                if ($it_value['item_id'] == $items_value->item_id) {
                    //在$reason_arr数组中插入退货原因
                    array_push($reason_arr, $items_value->reason);
                    //计算并插入每个商品退货总数
                    $it_value['return_num'] = $items_value->variation_quantity_purchased + $it_value['return_num'];
                    $array_item[$it_key]['return_num'] = $it_value['return_num'];
                }
            }

            //将退货原因插入$array_item数组（当前$array_item数组中存有item_id和reason字段）
            $array_item[$it_key]['reason'] = array_unique($reason_arr);

        }

        //遍历$array_item,按照原因字段组成新数组$return_data
        foreach ($array_item as $ai_k => $ai_v) {
            foreach ($ai_v['reason'] as $r_k => $r_v) {
                $return_param['item_id'] = $ai_v['item_id'];
                $return_param['return_num_total'] = $ai_v['return_num'];
                $return_param['reason'] = $r_v;
                $return_data[] = $return_param;
            }
        }
        //当前$return_data数组中存有商品编号item_id、每个产品退货总数return_num_total、退货原因reason字段
        //遍历$return_data，以item_id和reason字段为条件，根据$items数组中的值计算出每个商品每个原因的退货数量return_sum
        foreach ($return_data as $return_key => $return_value) {
            $return_data[$return_key]['return_sum'] = 0;
            foreach ($items as $ke => $val) {
                //判断item_id和reason分别相等
                if (($return_value['item_id'] == $val->item_id) && ($return_value['reason'] == $val->reason)) {
                    //计算每个商品每个原因的退货数量return_sum并插入$return_data数组
                    $return_data[$return_key]['return_sum'] += $val->variation_quantity_purchased;
                    //将商品名item_name插入$return_data数组
                    $return_data[$return_key]['item_name'] = $val->item_name;
                }
            }

            //计算当前产品$return_value['item_id']的总销量
            $sales = Db::table('shopee_order_items as soi')
                ->leftJoin('shopee_order as so', 'so.ordersn', 'soi.ordersn')
                ->where('soi.item_id', $return_value['item_id'])
                ->whereBetween('so.create_time', [$start_time, $end_date])
                ->where('so.shop_id', $data['shop_id'])
                ->sum('soi.variation_quantity_purchased');

            //插入销量
            $return_data[$return_key]['sales_volume'] = $sales;
            //计算每个产品每个原因的退货率 return_sum退货数量/sales_volume销售额
            $return_data[$return_key]['return_rate'] = (bcdiv($return_data[$return_key]['return_sum'], $return_data[$return_key]['sales_volume'], 4) * 100) . '%';
            //退货率大于百分之2变色预警
            if ($return_data[$return_key]['return_sum'] / $return_data[$return_key]['sales_volume'] > 0.02) {
                $return_data[$return_key]['is_rate_remind'] = 1;
            } else {
                $return_data[$return_key]['is_rate_remind'] = 0;
            }
//            var_dump($sales);
        }


        $return['list'] = $return_data;
        $return['month'] = $date_month . '月' . $week;
        return $return;


    }

    /**
     * shopee退款原因
     */
    public function shopee_return_reason($data)
    {
        $where = "1=1";
        $reason_array = [];
        //shop_id 店铺id
        if (!isset($data['shop_id'])) {
            $where_store = '';
            if($data['shopee_list_all']===false){
                $store_user = json_encode([$data['user_info']['Id']]);
                $where_store = " and store_user='{$store_user}'";
            }
            $shop_sql = "select Id,shop_name from shop where platform_id=7 and state=1".$where_store;
            $shop = json_decode(json_encode(db::select($shop_sql)), true);
            if (!$shop) {
                return '没有shopee店铺';
            }
            $data['shop_id'] = $shop[0]['Id'];

        }
        if (isset($data['shop_id'])) {
            $where .= " and `shop_id` = {$data['shop_id']}";
        }
        $shop_name = Db::table('shop')->where('Id', $data['shop_id'])->pluck('shop_name');

////        $data['date'] = '2022-02-15';
//        if (!isset($data['date'])) {
//            $date_now = date('Y-m', time());
//            $day = date('d', time());
//            //判断每周的第一天是几号
//            if ($day >= 1 && $day <= 7) {
//                $date_now .= '-01';
//            }
//            if ($day >= 8 && $day <= 14) {
//                $date_now .= '-08';
//            }
//            if ($day >= 15 && $day <= 21) {
//                $date_now .= '-15';
//            }
//            if ($day >= 22 && $day <= 31) {
//                $date_now .= '-22';
//            }
//            $data['date'] = $date_now;
//        }
//
//        //本期时间
////        $data['date'] = '2022-02-15';
//        $date = $data['date'];
//        //判断是第几周
//        $date_day = date('d', strtotime($date));
//
//        if ($date_day == '01') {
//            $week = '第一周';
//        } elseif ($date_day == '08') {
//            $week = '第二周';
//        } elseif ($date_day == '15') {
//            $week = '第三周';
//        } elseif ($date_day == '22') {
//            $week = '第四周';
//        }
//        //判断是几月份
//        $date_month = date('m', strtotime($date));
//
//        $start_time = $date . ' 00:00:00';
//        if ($date_day > 22 || $date_day == 22) {
//            $end_date = date('Y-m-t', strtotime($date)) . ' 23:59:59';
//        } else {
//            $end_date = date('Y-m-d', strtotime('+7 days', strtotime($date))) . ' 00:00:00';
//        }

        if (!isset($data['start_date']) || !isset($data['end_date'])) {
            $data['start_date'] = date('Y-m-d');
            $data['end_date'] = date('Y-m-d', strtotime('- 7 days'));
        }

        $startDateTime = $data['start_date'] . ' 00:00:00';//起始时间 日期格式
        $endDateTime = $data['end_date'] . ' 23:59:59'; //截止时间 日期格式

        //判断是第几周
        $date_day = date('d', strtotime($startDateTime));
        $week = '';
        if ($date_day == '01') {
            $week = '第一周';
        } elseif ($date_day == '08') {
            $week = '第二周';
        } elseif ($date_day == '15') {
            $week = '第三周';
        } elseif ($date_day == '22') {
            $week = '第四周';
        }
        //判断是几月份
        $date_month = date('m', strtotime($startDateTime));

        $start_time = $startDateTime;
        $end_date = $endDateTime;

        /*$sql = "select sr.ordersn,sr.reason ,count(sr.reason) as count
                    from shopee_returns sr
                    left join shopee_order so on so.ordersn=sr.ordersn
                    where so.create_time between '{$start_time}' and '{$end_date}'
                        and so.shop_id = {$data['shop_id']} and so.order_status in ('TO_RETURN','RETRY_SHIP')
                    GROUP BY sr.reason
                    order by count desc";*/
        //        $list = json_decode(json_encode(db::select($sql)), true);


        $order = Db::table('shopee_order')
            ->select('ordersn', 'cancel_by', 'cancel_reason', 'create_time')
            ->whereBetween('create_time', [$start_time, $end_date])
            ->where('shop_id', $data['shop_id'])
            ->where('order_status', 'CANCELLED')
            ->get()
            ->toArray();

//var_dump($order);die;
        $order_sn = array_column($order, 'ordersn');
        $return = Db::table('shopee_order_items')
            ->select('ordersn', 'variation_quantity_purchased')
            ->whereIn('ordersn', $order_sn)
            ->get()
            ->toArray();
//        $items_data = str_replace("'", '"', $return[0]->items);
        $reason_list = array_unique(array_column($order, 'cancel_reason'));
        foreach ($reason_list as $rea_key => $rea_val) {
            $array['reason'] = $rea_val;
            $reason_array[] = $array;
        }
//        var_dump($reason_array);die;
        foreach ($reason_array as $k => $v) {
            foreach ($order as $order_key => $order_value) {

                if ($order_value->cancel_reason == $v['reason']) {

                    $order_sn = $order_value->ordersn;

                    $order_sn_arr[] = $order_sn;
                    $reason_array[$k]['cancel_by'] = $order_value->cancel_by;
//                    var_dump($order_value->cancel_by);
                }

            }
            $reason_array[$k]['ordersn'] = $order_sn_arr;

            $order_sn_arr = [];

        }


        foreach ($reason_array as $reason_key => $reason_value) {

            if ($reason_value['cancel_by'] !== 'buyer') {
                $reason_array[$reason_key]['is_remind'] = 1;
            }
//            var_dump($reason_value['ordersn']);
            $return = Db::table('shopee_order_items')
                ->select('ordersn', 'variation_quantity_purchased')
                ->whereIn('ordersn', $reason_value['ordersn'])
                ->get();

            if (!$return->isEmpty()) {
                $arr = [];
                foreach ($return as $return_key => $return_value) {
//                    $items_data = json_decode(str_replace("'", '"', $return_value->items), true);
                    $items_data = $return_value->variation_quantity_purchased;
//                    $amount_total = sum($items_data);
//                    var_dump($items_data);die;

                    if (!empty($items_data)) {
                        //$amount = array_sum(array_column($items_data, 'amount'));
                        array_push($arr, $items_data);
                        $amount_total = array_sum($arr);
                    }


                }
                $reason_array[$reason_key]['sum'] = $amount_total;

            }//die;
            unset($reason_array[$reason_key]['ordersn']);

//            var_dump($return);die;
        }
        $total = array_sum(array_column($reason_array, 'sum'));
//        var_dump($reason_array);die;
        foreach ($reason_array as $k => $v) {
            if (!isset($v['sum'])) {
                unset($reason_array[$k]);
            } else {
                $reason_array[$k]['rate'] = round($v['sum'] / $total * 100, 2) . '%';
//                var_dump($v['sum'] / $total*100>20);
                /*if ($v['sum'] / $total * 100 > 20) {
                    $reason_array[$k]['is_remind'] = 1;
                } else {
                    $reason_array[$k]['is_remind'] = 0;
                }*/
                if ($v['reason'] == 'Failed Delivery' || $v['reason'] == 'Seller did not Ship' || $v['reason'] == 'Seller is not responsive to my inquiries' || $v['reason'] == 'Out of Stock' || $v['reason'] == 'Parcel lost in transit. The eligible compensation has been credited into your seller wallet.') {
                    $reason_array[$k]['is_remind'] = 1;
                } else {
                    $reason_array[$k]['is_remind'] = 0;
                }
                $reason_array[$k]['reason_detail'] = "{$v['cancel_by']}原因：{$v['reason']}";
            }


        }//die;

//        var_dump($reason_array);die;

        $return['list'] = $reason_array;
        $return['shop_name'] = $shop_name[0];
        $return['month'] = $date_month . '月' . $week;
        return $return;

    }

    /**
     * shopee广告组花费最高
     */
    public function shopee_adv_group($data)
    {
        $where = "1=1";
        $reason_array = [];
        //shop_id 店铺id
        if (!isset($data['shop_id'])) {
            $where_store = '';
            if($data['shopee_list_all']===false){
                $store_user = json_encode([$data['user_info']['Id']]);
                $where_store = " and store_user='{$store_user}'";
            }

            $shop_sql = "select Id,shop_name from shop where platform_id=7 and state=1".$where_store;
            $shop = json_decode(json_encode(db::select($shop_sql)), true);
            if (!$shop) {
                return '没有shopee店铺';
            }
            $data['shop_id'] = $shop[0]['Id'];

        }
        if (isset($data['shop_id'])) {
            $where .= " and `shop_id` = {$data['shop_id']}";
        }
        $shop_name = Db::table('shop')->where('Id', $data['shop_id'])->pluck('shop_name');

//        $data['date'] = '2022-02-15';
        if (!isset($data['start_date'])) {
            $data['start_date'] = date('Y-m-d', strtotime('last Friday'));
            $data['end_date'] = date('Y-m-d', strtotime('Thursday'));
        }

        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
//        var_dump($start_time.'-'.$end_time);die;
        $weekarray = array("日", "一", "二", "三", "四", "五", "六");
        if ($weekarray[date("w", strtotime($start_date))] != '五' ||
            $weekarray[date("w", strtotime($end_date))] != '四') {
            $a = "请选择起始日为周五,且结束日为周四的区间, 当前选择起始日为周{$weekarray[date("w", strtotime($start_date))]},结束日为周{$weekarray[date("w", strtotime($end_date))]}";
            return $a;

        }

        $sql = "select adv_name,product_id,keyword,show_num,click_num,
                    click_rate,goods_sold,conversion_rate,sales_amount_us,cost_us
                    from shopee_adv
                    where `start_date`='{$start_date}' and `end_date`='{$end_date}'
                        and shop_id={$data['shop_id']}
                    order by cost desc
                    limit 10";
//        var_dump($sql);die;
        $list = json_decode(json_encode(db::select($sql)), true);
        foreach ($list as $k => $v) {
            $list[$k]['conversion_rate'] = $v['conversion_rate'];
//            $list[$k]['click_rate'] = $v['click_rate'] . '%';
            $list[$k]['click_rate'] = round($v['click_num'] / $v['show_num'] * 100, 2) . '%';
            $list[$k]['acos'] = $v['sales_amount_us'] == 0 ? 0 : round($v['cost_us'] / $v['sales_amount_us'] * 100, 2) . '%';
            $list[$k]['roi'] = round($v['sales_amount_us'] / $v['cost_us'], 2);

            if ((float)$list[$k]['acos'] / 100 > 0.2) {
                $list[$k]['is_remind'] = 1;
            }
        }
        $return['list'] = $list;
        $return['shop_name'] = $shop_name[0];
        return $return;


    }

    public function shopee_adv_cost($data)
    {
        $where = "1=1";
        $reason_array = [];
        //shop_id 店铺id
        if (!isset($data['shop_id'])) {
            $where_store = '';
            if($data['shopee_list_all']===false){
                $store_user = json_encode([$data['user_info']['Id']]);
                $where_store = " and store_user='{$store_user}'";
            }

            $shop_sql = "select Id,shop_name from shop where platform_id=7 and state=1".$where_store;
            $shop = json_decode(json_encode(db::select($shop_sql)), true);
            if (!$shop) {
                return '没有shopee店铺';
            }
            $data['shop_id'] = $shop[0]['Id'];

        }
        if (isset($data['shop_id'])) {
            $where .= " and `shop_id` = {$data['shop_id']}";
        }
        $shop_name = Db::table('shop')->where('Id', $data['shop_id'])->pluck('shop_name');

//        $data['date'] = '2022-02-15';
        if (!isset($data['start_date'])) {
            $data['start_date'] = date('Y-m-d', strtotime('last Friday'));
            $data['end_date'] = date('Y-m-d', strtotime('Thursday'));
        }

        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
//        var_dump($start_time.'-'.$end_time);die;
        $weekarray = array("日", "一", "二", "三", "四", "五", "六");
        if ($weekarray[date("w", strtotime($start_date))] != '五' ||
            $weekarray[date("w", strtotime($end_date))] != '四') {
            $a = "请选择起始日为周五,且结束日为周四的区间, 当前选择起始日为周{$weekarray[date("w", strtotime($start_date))]},结束日为周{$weekarray[date("w", strtotime($end_date))]}";
            return $a;

        }

        /*$sql = "select product_id,adv_name,keyword,sum(show_num) as show_num_total,sum(click_num) as click_num_total,
                    sum(goods_sold) as goods_sold_total,
                    sum(conversion_rate) as conversion_rate_total,sum(sales_amount_us) as sales_amount_total,
                    sum(cost_us) as search_cost_total
                    from shopee_adv
                    where `start_date`='{$start_date}' and `end_date`='{$end_date}'
                        and shop_id={$data['shop_id']}
                    group by product_id";*/// order by cost desc  limit 10//sum(click_rate) as click_rate_total,//and adv_type in ('商品搜索广告','Product Search Ad')
        $sql = "select product_id,adv_name
                            from shopee_adv
                            where `start_date`='{$start_date}' and `end_date`='{$end_date}'
                                and shop_id={$data['shop_id']}
                            group by product_id";
        $list = json_decode(json_encode(db::select($sql)), true);

        foreach ($list as $k => $v) {
//            var_dump($v['product_id']);
            $sql = "select product_id,adv_name,keyword,show_num as show_num_total,click_num as click_num_total,
                    goods_sold as goods_sold_total,
                    conversion_rate as conversion_rate_total,sales_amount_us as sales_amount_total,
                    cost_us as search_cost_total
                    from shopee_adv
                    where `start_date`='{$start_date}' and `end_date`='{$end_date}'
                        and shop_id={$data['shop_id']} and adv_type in ('商品搜索广告','Product Search Ad')
                        and  product_id={$v['product_id']}";// order by cost desc  limit 10//sum(click_rate) as click_rate_total,


            $adv_product = json_decode(json_encode(db::select($sql)), true);

            $show_num_total = array_sum(array_column($adv_product, 'show_num_total'));
            $click_num_total = array_sum(array_column($adv_product, 'click_num_total'));
            $goods_sold_total = array_sum(array_column($adv_product, 'goods_sold_total'));
            $conversion_rate_total = array_sum(array_column($adv_product, 'conversion_rate_total'));
            $sales_amount_total = array_sum(array_column($adv_product, 'sales_amount_total'));
            $conversion_rate_total = array_sum(array_column($adv_product, 'conversion_rate_total'));
            $list[$k]['search_cost_total'] = round(array_sum(array_column($adv_product, 'search_cost_total')), 2);
            //  $list[$k]['adv_name'] = isset($adv_product[0])?$adv_product[0]['adv_name']:0;
            $list[$k]['keyword'] = isset($adv_product[0]) ? $adv_product[0]['keyword'] : 0;


            $product_id = $v['product_id'];
            $relation_sql = "select  sum(cost_us) as relation_cost_total,sum(show_num) as show_num_total,sum(click_num) as click_num_total,
                    sum(goods_sold) as goods_sold_total,
                    sum(conversion_rate) as conversion_rate_total,sum(sales_amount_us) as sales_amount_total
                    from shopee_adv
                    where `start_date`='{$start_date}' and `end_date`='{$end_date}'
                     and shop_id={$data['shop_id']} and adv_type in ('关联广告','Discovery Ads') and product_id = '{$product_id}'";
            $relation = json_decode(json_encode(db::select($relation_sql)), true);
//            var_dump($relation);
            if (!isset($relation[0]['relation_cost_total']) || empty($relation[0]['relation_cost_total'])) {
                $relation[0]['relation_cost_total'] = 0;
            }


            $list[$k]['show_num_total'] = round($relation[0]['show_num_total'] + $show_num_total, 2);
            $list[$k]['click_num_total'] = round($relation[0]['click_num_total'] + $click_num_total, 2);
            $list[$k]['goods_sold_total'] = round($relation[0]['goods_sold_total'] + $goods_sold_total, 2);
            $list[$k]['conversion_rate_total'] = round($relation[0]['conversion_rate_total'] + $conversion_rate_total, 2);
            $list[$k]['sales_amount_total'] = round($relation[0]['sales_amount_total'] + $sales_amount_total, 2);
            //$list[$k]['search_cost_total'] = $relation[0]['search_cost_total']+$search_cost_total;
            $list[$k]['relation_cost_total'] = $relation[0]['relation_cost_total'];
            $list[$k]['conversion_rate_total'] = $list[$k]['conversion_rate_total'] . '%';
//            $list[$k]['click_rate_total'] = $v['click_rate_total'] . '%';
            $list[$k]['click_rate_total'] = $list[$k]['show_num_total'] == 0 ? 0 : round($list[$k]['click_num_total'] / $list[$k]['show_num_total'] * 100, 2) . '%';
            $list[$k]['acos'] = $list[$k]['sales_amount_total'] == 0 ? 0 : round(($list[$k]['search_cost_total'] + $relation[0]['relation_cost_total']) / $list[$k]['sales_amount_total'] * 100, 2) . '%';
            $list[$k]['roi'] = $list[$k]['search_cost_total'] + $relation[0]['relation_cost_total'] == 0 ? 0 : round($list[$k]['sales_amount_total'] / ($list[$k]['search_cost_total'] + $relation[0]['relation_cost_total']), 2);
            $list[$k]['sum_cost'] = $list[$k]['search_cost_total'] + $relation[0]['relation_cost_total'];
            if ((float)$list[$k]['acos'] / 100 > 0.2) {
                $list[$k]['is_remind'] = 1;
            }
        }
        //var_dump($list);die;//die;die;


//        var_dump($list);die;

        if (!empty($list)) {
            $sort = array(
                'direction' => 'SORT_DESC', //排序顺序标志 SORT_DESC 降序；SORT_ASC 升序
                'field' => 'sum_cost',       //排序字段
            );

            $arrSort = array();
            foreach ($list as $uniqid => $row) {
                foreach ($row as $key => $value) {
                    $arrSort[$key][$uniqid] = $value;
                }
            }
            if ($sort['direction']) {
                array_multisort($arrSort[$sort['field']], constant($sort['direction']), $list);
            }
//        $list = array_slice($list, 0, 10);
        }


        $return['list'] = $list;
        $return['shop_name'] = $shop_name[0];
        return $return;

    }

    /**
     * shopee库存表
     */
    public function shopee_stock($data)
    {
        // $where = "1=1";
        $where = "";
        $where_item = "";


        $stock_condition = ' order by ss.ending_inventory desc limit 10';

        $moving_condition = '';
        if (isset($data['prop'])) {
            if ($data['prop'] == 'ending_inventory') {
                $stock_condition = " order by ss.ending_inventory {$data['order']} limit 10";
            }
            if ($data['prop'] == 'total_saves') {
                $stock_condition = '';

            }
            if ($data['prop'] == 'moving_sales_days') {
                $stock_condition = " order by ss.ending_inventory {$data['order']} limit 10";//and ending_inventory>150

            }

        }
        if (isset($data['country_id'])) {
            $data['country_id'] = json_encode([$data['country_id']]);
            $where .= " and s.country_id={$data['country_id']}";
            $country = Db::table('countrys')->where('id', $data['country_id'])->pluck('currency')->toArray();
            $where_item .= " and so.currency='{$country[0]}'";
        }
        //shop_id 店铺id
        if (!isset($data['shop_id'])&&!isset($data['country_id'])) {
            $where_store = '';
            if($data['shopee_list_all']===false){
                $store_user = json_encode([$data['user_info']['Id']]);
                $where_store = " and store_user='{$store_user}'";
            }

            $shop_sql = "select Id,shop_name from shop where platform_id=7 and state=1".$where_store;
            $shop = json_decode(json_encode(db::select($shop_sql)), true);
            if (!$shop) {
                return '没有shopee店铺';
            }

            $data['shop_id'] = array_column($shop, 'Id');
        }
//        if (isset($data['shop_id'])) {
//            $where .= " and `shop_id` = {$data['shop_id']}";
//        }
        if (!empty($data['shop_id'])) {
            $shop_id_str = implode(',', $data['shop_id']);
            $where .= " and ss.shop_id in ({$shop_id_str})";
            $where_item .= " and so.shop_id in ({$shop_id_str})";
            //$shop_name = Db::table('shop')->whereIn('Id', $data['shop_id'])->pluck('shop_name')->toArray();
        }

//        var_dump(implode(',',$shop_name));die;

//        $data['date'] = '2022-02-15';
        if (!isset($data['start_date'])) {
            $data['start_date'] = date('Y-m-d', strtotime('last Friday'));
            $data['end_date'] = date('Y-m-d', strtotime('Thursday'));

        }
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
//        var_dump($start_date.'-'.$end_date);die;
        $weekarray = array("日", "一", "二", "三", "四", "五", "六");
        if ($weekarray[date("w", strtotime($start_date))] != '五' ||
            $weekarray[date("w", strtotime($end_date))] != '四') {
            $a = "请选择起始日为周五,且结束日为周四的区间, 当前选择起始日为周{$weekarray[date("w", strtotime($start_date))]},结束日为周{$weekarray[date("w", strtotime($end_date))]}";
            return $a;

        }

        //表标题
        $title = '库存最高的10个产品';
        $start_time = $start_date . ' 00:00:00';
        $end_time = $end_date . ' 00:00:00';

        $last_date = date('Y-m-d', strtotime('-7 days', strtotime($start_date))) . ' 00:00:00';

//        var_dump($shop_id_str);die;
        //店鋪id,sku,产品名,库存数量，在途数量，期末库存价值，在途金额
        $stock_sql = "select 
                            shop_id,shop_name, sku, product_name, sum(ending_inventory) as ending_inventory, 
                            sum(quantity_transit) as quantity_transit, ending_inventory_price_us, amount_transit_us 
                        from shopee_stock ss
                        left join shop s on s.Id=ss.shop_id 
                        where ss.start_date='{$start_date}' and ss.end_date='{$end_date}'
                            {$where}
                            group by ss.sku
                        {$stock_condition}";//{$moving_condition}    and ss.shop_id in ({$shop_id_str})

//        var_dump($stock_sql);die;
        $stock = json_decode(json_encode(db::select($stock_sql)), true);
//        var_dump($stock);die;
//        var_dump($stock);die;
        $shop_name = array_unique(array_column($stock, 'shop_name'));
        $sku = array_column($stock, 'sku');
        //将数组拆分成带引号的字符串
        $sku_str = implode(',', array_map(
                function ($str) {
                    return sprintf("'%s'", $str);
                }, $sku
            )
        );
//        var_dump($stock_sql);die;
        if (empty($sku_str)) {
            return '没有库存数据';
        }
        $item_sql = "select 
                        soi.ordersn,soi.variation_sku,soi.item_name,so.create_time,sum(soi.variation_quantity_purchased) as total_saves,
                        sum(CASE WHEN soi.variation_discounted_price_us = 0.00 THEN soi.variation_original_price_us* soi.variation_quantity_purchased ELSE soi.variation_discounted_price_us* soi.variation_quantity_purchased end ) as total_price
                        from shopee_order_items soi
                        left join shopee_order so on soi.ordersn=so.ordersn
                        where so.create_time between '{$start_time}' and '{$end_time}'
                            and soi.variation_sku in ({$sku_str}) {$where_item}
                        group by soi.variation_sku";//so.shop_id in ({$shop_id_str})

//        var_dump($item_sql);die;
        $item = json_decode(json_encode(db::select($item_sql)), true);

//var_dump($shop_id_str);die;
        //上周销量
        $last_sql = "select 
                        soi.variation_sku,sum(soi.variation_quantity_purchased) as total_saves
                        from shopee_order_items soi
                        left join shopee_order so on soi.ordersn=so.ordersn
                        where so.create_time between '{$last_date}' and '{$start_time}'
                            and soi.variation_sku in ({$sku_str})
                        group by soi.variation_sku";
        $last_item = json_decode(json_encode(db::select($last_sql)), true);

        foreach ($stock as $key => $value) {
            $stock[$key]['last_total_saves'] = 0;
            $stock[$key]['total_saves'] = 0;
            $stock[$key]['moving_sales_days'] = 0;
            $stock[$key]['inventory_cost'] = bcadd($value['ending_inventory_price_us'], $value['amount_transit_us']);//库存成本
            foreach ($item as $k => $v) {
                if ($value['sku'] == $v['variation_sku']) {
                    $stock[$key]['total_saves'] = $v['total_saves'];//周销量
                    $stock[$key]['total_price'] = $v['total_price'];//销售额
                    $stock[$key]['moving_sales_days'] = round($value['ending_inventory'] / ($v['total_saves'] / 7), 2);//动销天数

                }
            }
            if ($stock[$key]['moving_sales_days'] > 60) {
                $stock[$key]['is_remind'] = 1;
            } elseif ($stock[$key]['moving_sales_days'] < 45) {
                $stock[$key]['is_remind'] = 2;
            }

            foreach ($last_item as $last_key => $last_value) {
                if ($value['sku'] == $last_value['variation_sku']) {
                    $stock[$key]['last_total_saves'] = $last_value['total_saves'];//上周销量
                } else {
                    $stock[$key]['last_total_saves'] = 0;
                }
            }
            $stock[$key]['growth_rate'] = $stock[$key]['last_total_saves'] == 0 ? 0 : round((bcsub($stock[$key]['total_saves'], $stock[$key]['last_total_saves']) / $stock[$key]['last_total_saves']) / 100, 2) . '%';
        }

//        var_dump($stock);die;
        if (isset($data['prop']) && $data['prop'] == 'total_saves') {
            if ($data['order'] == 'desc') {
                $direction = 'SORT_DESC';
            } else {
                $direction = 'SORT_ASC';
            }
            $sort = array(
                'direction' => $direction, //排序顺序标志 SORT_DESC 降序；SORT_ASC 升序
                'field' => 'total_saves',       //排序字段
            );

            $arrSort = array();
            foreach ($stock as $uniqid => $row) {
                foreach ($row as $key => $value) {
                    $arrSort[$key][$uniqid] = $value;
                }
            }
            if ($sort['direction']) {
                array_multisort($arrSort[$sort['field']], constant($sort['direction']), $stock);
            }
            $stock = array_slice($stock, 0, 10);
            $title = '销量最高的10个产品';
        }

        if (isset($data['prop']) && $data['prop'] == 'moving_sales_days') {
            if ($data['order'] == 'desc') {
                $direction = 'SORT_DESC';
            } else {
                $direction = 'SORT_ASC';
            }
            $sort = array(
                'direction' => $direction, //排序顺序标志 SORT_DESC 降序；SORT_ASC 升序
                'field' => 'moving_sales_days',       //排序字段
            );

            $arrSort = array();
            foreach ($stock as $uniqid => $row) {
                foreach ($row as $key => $value) {
                    $arrSort[$key][$uniqid] = $value;
                }
            }
            if ($sort['direction']) {
                array_multisort($arrSort[$sort['field']], constant($sort['direction']), $stock);
            }
            $stock = array_slice($stock, 0, 10);
            $title = '库存超过150的产品，库存天数最高的10个产品';
        }


        $return['list'] = $stock;
        $return['shop_name'] = implode(',', $shop_name);
        $return['title'] = $title;
        return $return;


    }

    /**
     * shopee个人毛利
     */
    public function shopee_gross_profit($data){
        $where = "1=1";
        //shop_id 店铺id
        if (!isset($data['shop_id']) && !isset($data['Id'])) {
            $where_store = '';
            if($data['shopee_list_all']===false){
                $store_user = json_encode([$data['user_info']['Id']]);
                $where_store = " and store_user='{$store_user}'";
            }
            $shop_sql = "select Id,shop_name from shop where platform_id=7 and state=1".$where_store;
            $shop = json_decode(json_encode(db::select($shop_sql)), true);
            if (!$shop) {
                return '没有shopee店铺';
            }
            $shop_id = array_column($shop, 'Id');
            $data['shop_id'] = $shop_id;
//            $data['shop_id'] = implode(',', $shop_id);
            //var_dump($data['shop_id']);die;
        }

        if (isset($data['user_id'])) {
            $data['user_id'] = json_encode([$data['user_id']]);
            $where .= " and store_user = '{$data['user_id']}'";
        }
//        var_dump($where);die;
        if (isset($data['shop_id'])) {
            $data['shop_id'] = implode(',', $data['shop_id']);
            $where .= " and Id in ({$data['shop_id']})";
        }
//        var_dump($where);die;



        $shop_data = Db::table('shop')->select('Id', 'shop_name')->whereRaw($where)->get()->toArray();
//        var_dump($shop_data);die;
//        $data['date'] = '2022-02-15';

        if (!isset($data['start_date'])) {
            $data['start_date'] = date('Y-m-d', strtotime('last Friday'));
            $data['end_date'] = date('Y-m-d', strtotime('Thursday'));
        }

        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
//        var_dump($start_time.'-'.$end_time);die;
        $weekarray = array("日", "一", "二", "三", "四", "五", "六");
        if ($weekarray[date("w", strtotime($start_date))] != '五' ||
            $weekarray[date("w", strtotime($end_date))] != '四') {
            $a = "请选择起始日为周五,且结束日为周四的区间, 当前选择起始日为周{$weekarray[date("w", strtotime($start_date))]},结束日为周{$weekarray[date("w", strtotime($end_date))]}";
            return $a;

        }
        //本期时间
//        $date = $data['date'];
        $start_time = $start_date . ' 00:00:00';
        $end_time = $end_date . ' 23:59:59';

//        var_dump($start_time);die;
        foreach ($shop_data as $shop_key => $shop_value) {
//            var_dump($shop_value);die;
            $shop_name = $shop_value->shop_name;
            $shop_id = $shop_value->Id;

            //查找shopee_link表数据
            //货币代码，平台成本=service_fee_us+voucher_seller_us+credit_card_transaction_fee_us+commission_fee_us
            $sql = "select so.currency,so.service_fee_us,so.voucher_seller_us,so.credit_card_transaction_fee_us,so.commission_fee_us,so.ordersn,so.create_time
                    from shopee_order so 
                    left join shop s on s.Id=so.shop_id
                    where so.order_status !='CANCELLED'
                    and (so.create_time between '{$start_time}' and '{$end_time}') and so.shop_id={$shop_id}
                 ";


            $list = json_decode(json_encode(db::select($sql)), true);

//            var_dump($list);die;

            $order_sn = array_column($list, 'ordersn');
//        var_dump($order_sn);die;
            $assets_data = [];
            if (!empty($shop_name)) {
                $assets_data['shop_name'] = $shop_name;
            }


            //销售额
            $total_price = Db::table('shopee_order')
                ->whereIn('ordersn', $order_sn)
                ->sum('variation_total_price_us');

            $item_list = Db::table('shopee_order_items as soi')
                ->select('soi.variation_sku', 'sc.cost_us', 'soi.item_id', 'sc.comprehensive_cost_us', 'soi.variation_discounted_price_us', 'soi.variation_quantity_purchased', 'soi.item_sku')
                ->leftJoin('shopee_cost as sc', 'sc.product_sku', '=', 'soi.variation_sku')
                ->whereIn('soi.ordersn', $order_sn)
                ->get()
                ->toArray();
            foreach ($item_list as $item_k => $item_v) {
                $item_list[$item_k]->cost_total = $item_v->cost_us * $item_v->variation_quantity_purchased;
            }
            //var_dump(array_sum(array_column($item_list, 'cost_us')));//die;
//            var_dump($item_list);die;
            $sku = Db::table('shopee_order_items')
                ->select('variation_sku')
                ->whereIn('ordersn', $order_sn)
                ->groupBy('variation_sku')
                ->get()
                ->toArray();
            $sku_arr = array_column($sku, 'variation_sku');
            $cost_data = Db::table('shopee_order_items as soi')
                ->select('sc.comprehensive_cost_us')
                ->leftJoin('shopee_order as so', 'so.ordersn', '=', 'soi.ordersn')
                ->leftJoin('shopee_cost as sc', 'sc.product_sku', '=', 'soi.variation_sku')
                ->where('so.shop_id', $shop_id)
                ->whereBetween('so.create_time', [$start_time, $end_time])
                ->first();

            // var_dump($cost_data->comprehensive_cost_us);die;
            $order_count = count($list);
//            $cost = round(array_sum(array_column($item_list, 'cost_us')), 2);//采购成本
//            $cost = round(array_sum(array_column($cost_data, 'cost_us')), 2);//采购成本
            $cost = round(array_sum(array_column($item_list, 'cost_total')), 2);//采购成本
//            $variation_discounted_price = round(array_sum(array_column($item_list, 'variation_discounted_price_us')), 2);//销售额
            $variation_discounted_price = round($total_price, 2);//销售额
            //$comprehensive_cost = round(array_sum(array_column($cost_data, 'comprehensive_cost_us')), 2);//综合成本
            //$comprehensive_cost = $cost_data;//综合成本
            $comprehensive_cost = !empty($cost_data) ? bcmul($cost_data->comprehensive_cost_us, $order_count, 2) : 0;//综合成本
//            $comprehensive_cost = round(array_sum(array_column($item_list, 'comprehensive_cost_us')), 2);//综合成本
            $variation_quantity_purchased = round(array_sum(array_column($item_list, 'variation_quantity_purchased')), 2);//销售量
            $item_id = array_filter(array_column($item_list, 'item_id'));
            $add_list = Db::table('shopee_adv')->select('cost_us')->where(['start_date' => $start_date, 'end_date' => $end_date, 'shop_id' => $shop_id])->get()->toArray();
//            var_dump($add_list);die;
            $adv_cost = round(array_sum(array_column($add_list, 'cost_us')), 2);//广告费
            $platform_cost = round(array_sum(array_column($list, 'service_fee_us')), 2) + round(array_sum(array_column($list, 'voucher_seller_us')), 2) + round(array_sum(array_column($list, 'credit_card_transaction_fee_us')), 2) + round(array_sum(array_column($list, 'commission_fee_us')), 2);//平台成本
//        $platform_cost = round(array_sum(array_column($list, 'service_fee'))+array_sum(array_column($list, 'voucher_seller_us'))+array_sum(array_column($list, 'credit_card_transaction_fee_us')),2);//平台成本


//            $assets_data['name'] = $name;
            $assets_data['variation_discounted_price'] = $variation_discounted_price;//销售额
            $assets_data['comprehensive_cost'] = $comprehensive_cost;//综合成本
            $assets_data['variation_quantity_purchased'] = $variation_quantity_purchased;//销售量
            $assets_data['adv_cost'] = $adv_cost;//广告费
            $assets_data['cost'] = $cost;//采购成本
            $assets_data['platform_cost'] = round($platform_cost, 2);//平台成本
            $assets_data['gross_profit'] = round($variation_discounted_price - $platform_cost - $cost - $comprehensive_cost - $adv_cost, 2);//毛利
            $assets_data['gross_rate'] = $variation_discounted_price == 0 ? 0 : round($assets_data['gross_profit'] / $variation_discounted_price * 100, 2) . '%';//毛利占比


            $assets_data[0]['shop_id'] = $shop_id;
            var_dump($assets_data);

            //店铺名
            $vacancies['shop_name'] = $shop_name;
            $vacancies['shop_id'] = $shop_id;
            //本周的数据集合
            $assets_data_arr[0][] = $assets_data[0];
            //上周的数据集合
//            $assets_data_arr[1][] = $assets_data[1];
            $vacancies_data[] = $vacancies;
        }die;

    }


    /**
     * 获取shopee店铺
     * @table shop
     * @param platform_id 平台（shopee平台==7）
     * @param state 店铺状态
     */
    public function shopee_shop($data)
    {

//        var_dump($data);die;
        if($data['shopee_list_all']===true){
            /*            $sql = "select Id,shop_name from shop where platform_id=7 and state=1";
                        $shop = json_decode(json_encode(db::select($sql)), true);*/
            $shop = Db::table('shop')->select('Id','shop_name')->where(['platform_id'=>7,'state'=>1])->get()->toArray();
        }else{
            $user_id = $data['user_info']['Id'];
            //如果没有所有用户的权限，查询当前账号在部门中的信息
            $organize = Db::table('organizes_member as om')
                ->select('om.type','om.organize_id','om.user_id')
                ->leftJoin('organizes as o','o.Id','=','om.organize_id')
                ->where(['o.type'=>1,'o.state'=>1,'om.user_id'=>$user_id])
                ->whereIn('o.Id',[20])
                ->get()
                ->toArray();
            $shop_user = json_encode([(string)$organize[0]->user_id]);
            $shop = Db::table('shop')
                ->select('Id','shop_name')
                ->where(['state'=>1,'store_user'=>$shop_user])
                ->get()
                ->toArray();


            //判断是type是否等于2，type=2为负责人权限，查找当前组的所有成员
            if(isset($organize[0]->type)&&($organize[0]->type==2)){
                /*                $shop_user = Db::table('organizes_member as om')
                                    ->leftJoin('users as u','u.Id','=','om.user_id')
                                    ->where(['om.organize_id'=>$shop[0]->organize_id,'u.state'=>1])
                                    ->get()
                                    ->toArray();
                                var_dump($shop_user);die;*/
                $shop = Db::table('shop as s')
                    ->select('s.Id','s.shop_name')
                    ->leftJoin('organizes_member as om','om.user_id','=','s.store_user')
                    ->leftJoin('users as u','u.Id','=','om.user_id')
                    ->where(['om.organize_id'=>$organize[0]->organize_id,'u.state'=>1,'s.platform_id' => 7, 's.state' => 1])
                    ->groupBy('s.Id')
                    ->get()
                    ->toArray();
            }

        }
        if(!$shop[0]->Id){
            $shop = [];
        }

        $return['list'] = $shop;
        return $return;


    }

    public function shopeeCostList($data)
    {

        $limit = "";
        if ((!empty($data['page'])) and (!empty($data['limit']))) {
            $limit = " limit " . ($data['page'] - 1) * $data['limit'] . ",{$data['limit']}";
        }

        $where = '1=1';
        //查询中文名
        if (isset($data['cnname'])) {
            $where .= " and sc.cnname like '%{$data['cnname']}%'";
        }
//var_dump($data);die;
        //查询映射sku
        if (isset($data['product_sku'])) {
            $where .= " and sc.product_sku='{$data['product_sku']}'";
        }
//        //查询sku
//        if(isset($data['sku'])){
//            $where .= " and sc.sku='{$data['sku']}'";
//        }
        //查询是否匹配到sku，1：已匹配 2：未匹配

        if (isset($data['is_sku'])) {
            if ($data['is_sku'] == 1) {
                $where .= " and sc.sku!=''";
            } else {
                $where .= " and sc.sku=''";
            }
        }
//        var_dump($data);die;

        $sql = "select SQL_CALC_FOUND_ROWS sc.*,soi.ordersn
					from shopee_cost sc
					left join shopee_order_items soi on soi.variation_sku=sc.product_sku
					where {$where}
					group by sc.product_sku
					order by sc.id asc
					{$limit}";
//        var_dump($sql);die;
        $list = json_decode(json_encode(db::select($sql)), true);
        ////总条数
        $count = db::select("SELECT FOUND_ROWS() as total");
        foreach ($list as $k => $v) {
            $list[$k]['shop_name'] = '';
            if (!empty($v['ordersn'])) {
                $shop_data = Db::table('shopee_order as so')
                    ->select('s.shop_name')
                    ->leftJoin('shop as s', 's.Id', '=', 'so.shop_id')
                    ->where('so.ordersn', $v['ordersn'])
                    ->get()
                    ->toArray();
                $list[$k]['shop_name'] = $shop_data[0]->shop_name;
            }

        }

//        var_dump($count);die;


        $return['list'] = $list;
        $return['total'] = $count[0]->total;
        return $return;

    }

    /**
     * shopee成本编辑
     */

    public function shopee_cost_update($data)
    {
        $currency_rate = Db::table('huilv')->select('dollar_rate', 'id')->where('currency', 'CNY')->orderBy('add_time', 'desc')->limit(1)->get()->toArray();
        $dollar_rate = $currency_rate[0]->dollar_rate;
        if (isset($data['cnname'])) {
            $param['cnname'] = $data['cnname'];//中文名
        } elseif (isset($data['sku'])) {
            $param['sku'] = $data['sku'];//sku
        } elseif (isset($data['cost'])) {
            $param['cost'] = $data['cost'];//成本
            $param['cost_us'] = bcmul($data['cost'], $dollar_rate, 2);//成本美金
            $param['huilv_id'] = $currency_rate[0]->id;
        } elseif (isset($data['comprehensive_cost'])) {
            $param['comprehensive_cost'] = $data['comprehensive_cost'];//运营费
            $param['comprehensive_cost_us'] = bcmul($data['comprehensive_cost'], $dollar_rate, 2);//运营费美金
            $param['huilv_id'] = $currency_rate[0]->id;
        }

        $id = $data['id'];

        if (isset($param)) {
            $res = Db::table('shopee_cost')->where('id', $id)->update($param);
            if ($res !== false) {
                return 1;
            }
        }

        return '编辑失败';

    }

    /**
     * 成本导出(导出没有映射sku的)
     */

    public function adv_excel_export()
    {


//        var_dump($where);die;
        $sql = "select soi.ordersn,sc.product_sku
                    from shopee_cost sc
                    left join shopee_order_items soi on soi.variation_sku=sc.product_sku
                    where sku=''
                    group by sc.product_sku 
                    order by sc.id asc";
        $list = json_decode(json_encode(DB::select($sql)), true);

        foreach ($list as $ka => $va) {
            $shop_data = Db::table('shopee_order as so')
                ->select('s.shop_name')
                ->leftJoin('shop as s', 's.Id', '=', 'so.shop_id')
                ->where('so.ordersn', $va['ordersn'])
                ->get()
                ->toArray();
            $list[$ka]['shop_name'] = $shop_data[0]->shop_name;
            //var_dump($va);die;
            $list[$ka]['ordersn'] = $va['ordersn'];
            $list[$ka]['product_sku'] = $va['product_sku'];
            $list[$ka]['sku'] = '';
            $list[$ka]['cost'] = '';
            $list[$ka]['cnname'] = '';
            $list[$ka]['comprehensive_cost'] = '';
        }


//		return $list;
        $title = [
            'shop_name' => '店铺',
            'ordersn' => '订单号',
            'product_sku' => '映射sku',
            'sku' => 'SKU',
            'cost' => '成本',
            'cnname' => '产品名称',
            'comprehensive_cost' => '综合',
        ];
        error_reporting(E_ALL);
        date_default_timezone_set('Europe/London');
        $objPHPExcel = new \PHPExcel();
        $PHPExcel_Style_Alignment = new\PHPExcel_Style_Alignment;

        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ");
        // 设置excel标题
        $index = 0;
        foreach ($title as $k => $v) {
            $objPHPExcel->getActiveSheet()->getStyle('A1:AU1')->getFont()->setBold(true);//第一行是否加粗
            $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);    //第一行行高
            $objPHPExcel->getActiveSheet()->setCellValue($cellName[$index] . "1", $v);
            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(60);
            $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
            $index++;
        }
        //var_dump($list);die;
        foreach ($list as $k => $v) {
            $num = $k + 2;
            //字体大小
            $objPHPExcel->getActiveSheet()
                ->getStyle('A' . $num . ':' . 'AY' . $num)
                ->getFont()
                ->setSize(10);
            //设置水平居中
            $objPHPExcel->getActiveSheet()
                ->getStyle('A' . $num . ':' . 'AY' . $num)
                ->getAlignment()
                ->setHorizontal($PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //设置垂直居中
            $objPHPExcel->getActiveSheet()
                ->getStyle('A' . $num . ':' . 'AY' . $num)
                ->getAlignment()
                ->setVertical($PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //自动换行
            $objPHPExcel->getActiveSheet()
                ->getStyle('A' . $num . ':' . 'AY' . $num)
                ->getAlignment()
                ->setWrapText(true);

            // 重置列索引起始位
            $i = 0;
            foreach ($title as $key => $value) {
                $objPHPExcel->setActiveSheetIndex(0)//Excel的第A列，uid是你查出数组的键值，下面以此类推
                ->setCellValue($cellName[$i] . $num, $v[$key]);
                $i++;
            }
        }
        $objPHPExcel->getActiveSheet()->setTitle('work');
        $objPHPExcel->setActiveSheetIndex(0);

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename="shopee成本和运营费.xls"');
        header('Cache-Control: max-age=1');
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter->save('php://output');
        exit();
    }

    /**
     * 获取国家
     */
    public function country_list($data)
    {


        if($data['shopee_list_all']===true){
            $country = Db::table('shop as s')
                ->select('c.Id', 'c.name')
                ->leftJoin('countrys as c', 's.country_id', '=', 'c.Id')
                ->where(['s.platform_id' => 7, 's.state' => 1])
                ->groupBy('s.country_id')
                ->get()
                ->toArray();
        }else{
            $user_id = $data['user_info']['Id'];
            //如果没有所有用户的权限，查询当前账号在部门中的信息
            $country_data = Db::table('organizes_member as om')
                ->select('om.type','om.user_id as Id','u.account','om.organize_id')
                ->leftJoin('organizes as o','o.Id','=','om.organize_id')
                ->leftJoin('users as u','u.Id','=','om.user_id')
                ->where(['o.type'=>1,'o.state'=>1,'om.user_id'=>$user_id])
                ->whereIn('o.Id',[20])
                ->get()
                ->toArray();
            $user = json_encode([(string)$country_data[0]->Id]);
//            var_dump($user);die;
            $country = Db::table('countrys as c')
                ->select('c.Id','c.name')
                ->leftJoin('shop as s','s.country_id','=','c.Id')
                ->where(['s.store_user'=>$user,'s.state'=>1])
                ->get()
                ->toArray();

            //判断是type是否等于2，type=2为负责人权限，查找当前组的所有成员
            if(isset($country_data[0]->type)&&($country_data[0]->type==2)){

                $organize_data = Db::table('organizes_member as om')
                    ->select('om.user_id')
                    ->leftJoin('users as u','u.Id','=','om.user_id')
                    ->leftJoin('organizes as o','o.Id','=','om.organize_id')
                    ->where(['o.type'=>1,'o.state'=>1,'om.organize_id'=>$country_data[0]->organize_id,'u.state'=>1])
                    ->get()
                    ->toArray();

                foreach ($organize_data as $key=>$value){
                    $user_arr = json_encode([(string)$value->user_id]);
                    $organize_data[$key] = $user_arr;
                }
//                var_dump($organize_data);die;

                $country = Db::table('countrys as c')
                    ->select('c.Id','c.name')
                    ->leftJoin('shop as s','s.country_id','=','c.Id')
                    ->whereIn('s.store_user',$organize_data)
                    ->where(['c.state'=>1,'s.state'=>1])
                    ->groupBy('c.Id')
                    ->get()
                    ->toArray();

                /*


                                $country = Db::table('shop as s')
                                    ->select('c.Id as Id','c.name')
                                    ->leftJoin('organizes_member as om','s.store_user','=','om.user_id')
                                    ->leftJoin('organizes as o','o.Id','=','om.organize_id')
                                    ->leftJoin('countrys as c','c.Id','=','s.country_id')
                                    ->where(['o.type'=>1,'o.state'=>1,'om.organize_id'=>$country[0]->organize_id,'s.platform_id' => 7, 's.state' => 1])
                                    ->whereIn('o.Id',[20])
                                    ->groupBy('c.Id')
                                    ->get()
                                    ->toArray();*/

            }

        }


        $return['list'] = $country;
        return $return;
    }

    /**
     * 获取用户
     */
    public function user_list($data)
    {


        //是否有查看所有用户数据的权限
        if($data['shopee_list_all']===false){
            $user_id = $data['user_info']['Id'];
            //如果没有所有用户的权限，查询当前账号在部门中的信息
            $user = Db::table('organizes_member as om')
                ->select('om.type','om.user_id as Id','u.account','om.organize_id')
                ->leftJoin('organizes as o','o.Id','=','om.organize_id')
                ->leftJoin('users as u','u.Id','=','om.user_id')
                ->where(['o.type'=>1,'o.state'=>1,'om.user_id'=>$user_id])
                ->whereIn('o.Id',[20])
                ->get()
                ->toArray();
//            var_dump(isset($user[0]->type));die;
            //判断是type是否等于2，type=2为负责人权限，查找当前组的所有成员
            if(isset($user[0]->type)&&$user[0]->type==2){
                $user = Db::table('organizes_member as om')
                    ->select('om.type','om.user_id as Id','u.account','om.organize_id')
                    ->leftJoin('organizes as o','o.Id','=','om.organize_id')
                    ->leftJoin('users as u','u.Id','=','om.user_id')
                    ->where(['o.type'=>1,'o.state'=>1,'om.organize_id'=>$user[0]->organize_id])
                    ->get()
                    ->toArray();
            }
        }else{

            $user = Db::table('organizes_member as om')
                ->select('om.type','om.user_id as Id','u.account','om.organize_id')
                ->leftJoin('organizes as o','o.Id','=','om.organize_id')
                ->leftJoin('users as u','u.Id','=','om.user_id')
                ->where(['o.type'=>1,'o.state'=>1,'u.state'=>1])
                ->whereIn('o.Id',[20])
                ->get()
                ->toArray();

            /* $user_data = Db::table('shop as s')
                 //->select('s.store_user','u.Id', 'u.account')
                 ->where(['s.platform_id' => 7, 's.state' => 1])
                 ->groupBy('s.store_user')
                 ->pluck('s.store_user')
                 ->toArray();
             foreach ($user_data as $key=>$value){
                 $user_arr = json_decode($value);
                 $user_data[$key] = $user_arr[0];
             }
             $user = Db::table('users')->select('Id','account')->whereIn('Id',$user_data)->get()->toArray();*/

        }
        $return['list'] = $user;
        return $return;
    }


    /**
     * 按照时间查询最高销量，找出对应产品
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid      店铺
     */
    public function mostProduct($startDateTime, $endDateTime, $shopid)
    {
        $sql = "select sum(i.variation_quantity_purchased)  as nums_all,item_id from shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid and  o.order_status in $this->status   group by item_id order by nums_all desc limit 10   ";
        //  echo $sql;
        $arr = db::select($sql);
        // var_dump($arr);
        return $arr;
    }


    /**
     * 按照时间查询最高销量，找出对应产品
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid      店铺
     */
    public function mostProductBySku($startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased)  as nums_all,i.variation_sku from shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ") and  o.order_status in $this->status  and i.variation_sku<>''  group by i.variation_sku order by nums_all desc limit 10   ";
            //   echo   $sql,PHP_EOL;


        } else {
            $sql = "select sum(i.variation_quantity_purchased)  as nums_all,i.variation_sku from shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid and  o.order_status in $this->status   and i.variation_sku<>''  group by i.variation_sku   order by nums_all desc limit 10   ";

        }


        $arr = db::select($sql);
        // var_dump($arr);
        return $arr;
    }


    /**
     * 按照时间查询最高销量，找出对应产品
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid      店铺
     */
    public function mostProductByName($startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased)  as nums_all,c.cnname from shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
    inner join shopee_cost as c on c.product_sku=i.variation_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "' 
and  o.shop_id in (" . implode(",", $shopid) . ") and  o.order_status in $this->status   and c.cnname!=''   group by c.cnname order by nums_all desc limit 10   ";

            //  exit();

        } else {
            $sql = "select sum(i.variation_quantity_purchased)  as nums_all,c.cnname from shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
    inner join shopee_cost as c on c.product_sku=i.variation_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid and  o.order_status in $this->status   and c.cnname!=''   group by c.cnname order by nums_all desc limit 10   ";

        }
        // echo $sql,PHP_EOL;
        $arr = db::select($sql);
        // var_dump($arr);
        return $arr;
    }


    /**
     * 统计商品的订单数
     * @param $itemId         shopee_item_item的item_id字段
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid      店铺
     */
    public function ordersNumByItemTime($itemId, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select count(i.ordersn) as countorder from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'      and  o.shop_id in (" . implode(",", $shopid) . ")  and i.item_id=$itemId  and  o.order_status in $this->status   ";
            // echo   $sql,PHP_EOL;
            $arr = db::select($sql);
        } else {
            $sql = "select count(i.ordersn) as countorder from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'      and o.shop_id=$shopid  and i.item_id=$itemId  and  o.order_status in $this->status   ";
            $arr = db::select($sql);
        }


        return $arr[0];
    }

    /**
     * 统计商品的订单数
     * @param $itemId         shopee_item_item的item_id字段
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid      店铺
     */
    public function ordersNumBySkuTime($sku, $startDateTime, $endDateTime, $shopid)
    {

        if (is_array($shopid)) {
            $sql = "select count(ordersn) as countorder  FROM shopee_order  
where create_time>='" . $startDateTime . "' and create_time<'" . $endDateTime . "'      and  shop_id in (" . implode(",", $shopid) . ")   and  order_status in $this->status  and ordersn in (select ordersn from shopee_order_items where variation_sku='" . $sku . "')  ";

            $arr = db::select($sql);
            return $arr[0];

        } else {

            $sql = "select count(ordersn) as countorder FROM  shopee_order  where create_time>='" . $startDateTime . "' and create_time<'" . $endDateTime . "'      and  shop_id=$shopid    and  order_status in $this->status  and ordersn in (select ordersn from shopee_order_items where variation_sku='" . $sku . "')   ";
            $arr = db::select($sql);
            return $arr[0];
        }
    }


    /**
     * 统计商品的订单数
     * @param $name
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid      店铺
     */
    public function ordersNumByName($name, $startDateTime, $endDateTime, $shopid)
    {

        if (is_array($shopid)) {
            $sql = "select count(i.ordersn) as countorder from  shopee_order_items as i
    inner  join shopee_order as o on o.ordersn=i.ordersn 
inner  join shopee_cost as c on c.product_sku=i.variation_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    
 and o.shop_id in (" . implode(",", $shopid) . ")  and c.cnname='" . $name . "' 
   and  o.order_status in $this->status   ";
            // echo $sql,PHP_EOL;
            /// exit();

        } else {
            $sql = "select count(i.ordersn) as countorder from  shopee_order_items as i
    inner  join shopee_order as o on o.ordersn=i.ordersn 
inner  join shopee_cost as c on c.product_sku=i.variation_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    
  and o.shop_id=$shopid  and c.cnname='" . $name . "' 
   and  o.order_status in $this->status   ";
        }


        $arr = db::select($sql);
        return $arr[0];
    }


    /**
     * 获取销售额
     * @param $itemId         shopee_item_item的item_id字段
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid      店铺
     */
    public function salePriceByItemTime($itemId, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {

            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'         and o.shop_id in (" . implode(",", $shopid) . ")  and i.item_id=$itemId and  o.order_status in $this->status   ";

        } else {
            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'       and o.shop_id=$shopid  and i.item_id=$itemId and  o.order_status in $this->status   ";

        }
        $arr = db::select($sql);
        return $arr[0];
    }

    /**
     * 获取销售额
     * @param $itemId         shopee_item_item的item_id字段
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid      店铺
     */
    public function salePriceBySkuTime($sku, $startDateTime, $endDateTime, $shopid)
    {
//        var_dump($startDateTime);
//        var_dump($endDateTime);
        if (is_array($shopid)) {

            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ")   and i.variation_sku='" . $sku . "' and  o.order_status in $this->status   ";

        } else {
            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'       and o.shop_id=$shopid  and i.variation_sku='" . $sku . "' and  o.order_status in $this->status   ";

        }
        //  echo $sql, PHP_EOL;
        // echo PHP_EOL;
        //  exit();

        $arr = db::select($sql);
        return $arr[0];
    }


    /**
     * 获取销售额
     * @param $itemId         shopee_item_item的item_id字段
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid      店铺
     */
    public function salePriceByNameTime($name, $startDateTime, $endDateTime, $shopid)
    {

        if (is_array($shopid)) {
            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
    inner  join shopee_cost as c on c.product_sku=i.variation_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     
  and o.shop_id in (" . implode(",", $shopid) . ")   and c.cnname='" . $name . "' and  o.order_status in $this->status   ";

        } else {
            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
    inner  join shopee_cost as c on c.product_sku=i.variation_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     
  and o.shop_id=$shopid  and c.cnname='" . $name . "' and  o.order_status in $this->status   ";
        }

        $arr = db::select($sql);
        return $arr[0];
    }


    /**
     * 获取销量
     * @param $itemId        shopee_item_item的item_id字段
     * @param $startDateTime  开始时间
     * @param $endDateTime   结束时间
     * @param $shopid   店铺
     * @return mixed
     */
    public function numItems($itemId, $startDateTime, $endDateTime, $shopid)
    {

        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and  o.shop_id in (" . implode(",", $shopid) . ")  and i.item_id=$itemId and  o.order_status in $this->status     ";
        } else {
            $sql = "select sum(i.variation_quantity_purchased)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and i.item_id=$itemId and  o.order_status in $this->status     ";
        }

        $arr = db::select($sql);
        return $arr[0];
    }

    /**
     * 获取销量
     * @param $itemId        shopee_item_item的item_id字段
     * @param $startDateTime  开始时间
     * @param $endDateTime   结束时间
     * @param $shopid   店铺
     * @return mixed
     */
    public function numSku($sku, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and  o.shop_id in (" . implode(",", $shopid) . ")   and i.variation_sku='" . $sku . "' and  o.order_status in $this->status     ";
            //  echo   $sql,PHP_EOL;
        } else {
            $sql = "select sum(i.variation_quantity_purchased)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and i.variation_sku='" . $sku . "' and  o.order_status in $this->status     ";
        }

        $arr = db::select($sql);
        return $arr[0];
    }

    /**
     * 获取销量
     * @param $itemId        shopee_item_item的item_id字段
     * @param $startDateTime  开始时间
     * @param $endDateTime   结束时间
     * @param $shopid   店铺
     * @return mixed
     */
    public function numName($name, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased)  as num from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
    inner  join shopee_cost as c on c.product_sku=i.variation_sku
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and  o.shop_id in (" . implode(",", $shopid) . ")   and c.cnname='" . $name . "' and  o.order_status in $this->status     ";
            //  echo $sql,PHP_EOL;
            // exit();
        } else {
            $sql = "select sum(i.variation_quantity_purchased)  as num from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
    inner  join shopee_cost as c on c.product_sku=i.variation_sku
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and c.cnname='" . $name . "' and  o.order_status in $this->status     ";

        }

        $arr = db::select($sql);
        return $arr[0];
    }


    /**
     * 取消订单数
     * @param $itemId    shopee_item_item的item_id字段
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid    店铺
     * @return mixed
     */
    public function cancelOrderNum($itemId, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and  o.shop_id in (" . implode(",", $shopid) . ")  and i.item_id=$itemId  and  o.order_status in ('TO_RETURN','IN_CANCEL')  ";
        } else {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and i.item_id=$itemId  and  o.order_status in ('TO_RETURN','IN_CANCEL')  ";
        }

        $arr = db::select($sql);
        return $arr[0];
    }


    /**
     * 取消订单数
     * @param $itemId    shopee_item_item的item_id字段
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid    店铺
     * @return mixed
     */
    public function cancelOrderNumBySku($sku, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ")  and i.variation_sku='" . $sku . "'  and  o.order_status in ('TO_RETURN','IN_CANCEL')  ";
            // echo   $sql,PHP_EOL;
        } else {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and i.variation_sku='" . $sku . "'  and  o.order_status in ('TO_RETURN','IN_CANCEL')  ";

        }

        $arr = db::select($sql);
        return $arr[0];
    }


    /**
     * 取消订单数
     * @param $itemId    shopee_item_item的item_id字段
     * @param $startDateTime   开始时间
     * @param $endDateTime     结束时间
     * @param $shopid    店铺
     * @return mixed
     */
    public function cancelOrderNumByName($name, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
    inner  join shopee_cost as c on c.product_sku=i.variation_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ") and c.cnname='" . $name . "'  and  o.order_status in ('TO_RETURN','IN_CANCEL')  ";
            //  echo $sql,PHP_EOL;
            // exit();
        } else {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
    inner  join shopee_cost as c on c.product_sku=i.variation_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and c.cnname='" . $name . "'  and  o.order_status in ('TO_RETURN','IN_CANCEL')  ";

        }

        $arr = db::select($sql);
        return $arr[0];
    }


    /**
     * 退货订单数
     * @param $itemId  shopee_item_item的item_id字段
     * @param $startDateTime  开始时间
     * @param $endDateTime     结束时间
     * @param $shopid    店铺
     * @return mixed
     */
    public function returnOrderNum($itemId, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ")  and i.item_id=$itemId  and  o.order_status='TO_RETURN'  ";

        } else {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and i.item_id=$itemId  and  o.order_status='TO_RETURN'  ";

        }

        $arr = db::select($sql);
        return $arr[0];
    }


    /**
     * 退货订单数
     * @param $itemId  shopee_item_item的item_id字段
     * @param $startDateTime  开始时间
     * @param $endDateTime     结束时间
     * @param $shopid    店铺
     * @return mixed
     */
    public function returnOrderNumBySku($sku, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ")  and i.variation_sku='" . $sku . "'  and  o.order_status='TO_RETURN'  ";
            // echo   $sql,PHP_EOL;
        } else {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and i.variation_sku='" . $sku . "'  and  o.order_status='TO_RETURN'  ";

        }


        $arr = db::select($sql);
        return $arr[0];
    }

    /**
     * 退货订单数
     * @param $itemId  shopee_item_item的item_id字段
     * @param $startDateTime  开始时间
     * @param $endDateTime     结束时间
     * @param $shopid    店铺
     * @return mixed
     */
    public function returnOrderNumByName($name, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn
    inner  join shopee_cost as c on c.product_sku=i.variation_sku
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ")  and c.cnname='" . $name . "'  and  o.order_status='TO_RETURN'  ";
            // echo $sql,PHP_EOL;
            //  exit();
        } else {
            $sql = "select count(i.ordersn)  as num from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn
    inner  join shopee_cost as c on c.product_sku=i.variation_sku
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and c.cnname='" . $name . "'  and  o.order_status='TO_RETURN'  ";

        }

        $arr = db::select($sql);
        return $arr[0];
    }


    /**
     * 毛利率
     * @param $itemId        shopee_item_item的item_id字段
     * @param $startDateTime  开始时间
     * @param $endDateTime   结束时间
     * @param $shopid      店铺
     * @param $priceTotal  销售额
     */
    public function shopeeLirun($priceTotal, $itemId, $startDateTime, $endDateTime, $shopid)
    {
        $startTime = strtotime($startDateTime);
        $startForAdv = date("d/m/Y H:i:s");
        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased*(c.cost+c.comprehensive_cost))  as cost_goods,sum(o.credit_card_transaction_fee) as card_fee_total,sum(o.service_fee_us) as service_fee_total   from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   inner  join shopee_cost as c   on i.variation_sku=c.product_sku     where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ")  and i.item_id=$itemId and  o.order_status in $this->status     ";
        } else {
            $sql = "select sum(i.variation_quantity_purchased*(c.cost+c.comprehensive_cost))  as cost_goods,sum(o.credit_card_transaction_fee) as card_fee_total,sum(o.service_fee_us) as service_fee_total   from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   inner  join shopee_cost as c   on i.variation_sku=c.product_sku     where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and i.item_id=$itemId and  o.order_status in $this->status     ";

        }

        $arr = db::select($sql);
        $row = $arr[0];


        $sql = "select sum(cost) as ad_fee  from  shopee_adv where startdate>'" . $startForAdv . "' and product_id=$itemId and  shop_id=$shopid  ";
        $arr = db::select($sql);
        // $rowAd = $arr[0];
        if (isset($arr[0])) {
            $adFee = $arr[0]->ad_fee;
        } else {
            $adFee = 0;
        }

        $lirun = round(($priceTotal - $row->cost_goods - $row->card_fee_total - $row->service_fee_total - $adFee), 2);
        return $lirun;
    }


    /**
     * 订单成本
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function shopeeGoodsCost($sku, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased) as item_num_all,c.cost_us
     ,sum(o.credit_card_transaction_fee_us) as card_fee_total,sum(o.service_fee_us) as service_fee_total  
from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   
    inner  join shopee_cost as c   on i.variation_sku=c.product_sku     where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ")  and i.variation_sku='" . $sku . "' and  o.order_status in $this->status     ";
        } else {
            $sql = "select sum(i.variation_quantity_purchased) as item_num_all, (c.cost_us+c.comprehensive_cost_us)  as cost_goods_unit
     ,sum(o.credit_card_transaction_fee_us) as card_fee_total,sum(o.service_fee_us) as service_fee_total  
from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   
    inner  join shopee_cost as c   on i.variation_sku=c.product_sku     where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id= $shopid and i.variation_sku='" . $sku . "' and  o.order_status in $this->status     ";
        }

        $arr = db::select($sql);
        $row = $arr[0];
        if (isset($row->item_num_all) && isset($row->cost_us)) {
            $cost_goods = $row->item_num_all * $row->cost_us;
        } else {
            $cost_goods = 0;
        }
        return $cost_goods;
    }


    /**
     * 订单成本
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function shopeeGoodsCostByName($name, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased*c.cost_us) as cost_goods
from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   
    inner  join shopee_cost as c   on i.variation_sku=c.product_sku     where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ")  and c.cnname='" . $name . "' and  o.order_status in $this->status     ";
        } else {
            $sql = "select sum(i.variation_quantity_purchased*c.cost_us) as cost_goods
from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   
    inner  join shopee_cost as c   on i.variation_sku=c.product_sku     where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id=$shopid  and c.cnname='" . $name . "' and  o.order_status in $this->status     ";
        }
        // echo $sql,PHP_EOL;
        $arr = db::select($sql);
        $row = $arr[0];
        $cost_goods = $row->cost_goods;
//        if (isset($row->item_num_all) && isset($row->cost_us)) {
//            $cost_goods = $row->item_num_all * $row->cost_us;
//        } else {
//            $cost_goods = 0;
//        }
        return $cost_goods;
    }


    /**
     * 订单成本
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function shopeeGoodsCostByNameQuick($name, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased) as item_num_all,c.cost_us
     
from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   
    inner  join shopee_cost as c   on i.variation_sku=c.product_sku     where i.create_time>='" . $startDateTime . "' and i.create_time<'" . $endDateTime . "'    and i.shop_id in (" . implode(",", $shopid) . ")  and c.cnname='" . $name . "' and  i.order_status in $this->status     ";
        } else {

        }
        // echo $sql,PHP_EOL;

        $arr = db::select($sql);
        $row = $arr[0];
        if (isset($row->item_num_all) && isset($row->cost_us)) {
            $cost_goods = $row->item_num_all * $row->cost_us;
        } else {
            $cost_goods = 0;
        }
        return $cost_goods;
    }


    /**
     * 订单其他费用
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function OrderOtherFee($sku, $startDateTime, $endDateTime, $shopid, $orders)
    {
        $OrderOtherFee = 0;
        foreach ($orders as $ordersn) {
            $sql = "select count(*) as num from shopee_order_items where ordersn='" . $ordersn . "'";
            $arr = db::select($sql);
            $itemAllNum = $arr[0]->num;

            $sql = "select count(*) as num  from shopee_order_items where ordersn='" . $ordersn . "' and variation_sku='" . $sku . "' ";
            $arr = db::select($sql);
            $skuNum = $arr[0]->num;

            $sql = "select credit_card_transaction_fee_us,service_fee_us,escrow_tax_us,commission_fee_us  from shopee_order where ordersn='" . $ordersn . "' ";
            $arr = db::select($sql);
            $credit_card_transaction_fee_us = $arr[0]->credit_card_transaction_fee_us;
            $service_fee_us = $arr[0]->service_fee_us;
            $escrow_tax_us = $arr[0]->escrow_tax_us;
            $commission_fee_us = $arr[0]->commission_fee_us;
            $OrderOtherFee = $OrderOtherFee + (($skuNum / $itemAllNum) * $credit_card_transaction_fee_us);
            $OrderOtherFee = $OrderOtherFee + (($skuNum / $itemAllNum) * $service_fee_us);
            $OrderOtherFee = $OrderOtherFee + (($skuNum / $itemAllNum) * $escrow_tax_us);
            $OrderOtherFee = $OrderOtherFee + (($skuNum / $itemAllNum) * $commission_fee_us);
        }
        return $OrderOtherFee;
    }

    /**
     * 订单其他费用
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function OrderOtherFeeByName($skus, $startDateTime, $endDateTime, $shopid, $orders)
    {
        $OrderOtherFee = 0;
        foreach ($skus as $sku) {
            foreach ($orders as $ordersn) {
                $sql = "select count(*) as num from shopee_order_items where ordersn='" . $ordersn . "'";
                $arr = db::select($sql);
                $itemAllNum = $arr[0]->num;

                $sql = "select count(*) as num  from shopee_order_items where ordersn='" . $ordersn . "' and variation_sku='" . $sku . "' ";
                $arr = db::select($sql);
                $skuNum = $arr[0]->num;

                $sql = "select credit_card_transaction_fee_us,service_fee_us,escrow_tax_us,commission_fee_us  from shopee_order where ordersn='" . $ordersn . "' ";
                $arr = db::select($sql);
                $credit_card_transaction_fee_us = $arr[0]->credit_card_transaction_fee_us;
                $service_fee_us = $arr[0]->service_fee_us;
                $escrow_tax_us = $arr[0]->escrow_tax_us;
                $commission_fee_us = $arr[0]->commission_fee_us;
                $OrderOtherFee = $OrderOtherFee + (($skuNum / $itemAllNum) * $credit_card_transaction_fee_us);
                $OrderOtherFee = $OrderOtherFee + (($skuNum / $itemAllNum) * $service_fee_us);
                $OrderOtherFee = $OrderOtherFee + (($skuNum / $itemAllNum) * $escrow_tax_us);
                $OrderOtherFee = $OrderOtherFee + (($skuNum / $itemAllNum) * $commission_fee_us);
            }
        }
        return $OrderOtherFee;
    }

    /**
     * @param $name
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function OrderOtherFeeByNameQuick($name, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(i.credit_card_transaction_fee_us)  as credit_card_transaction_fee_us,sum(i.service_fee_us) as service_fee_us,sum(i.escrow_tax_us) as  escrow_tax_us,sum(i.commission_fee_us) as commission_fee_us 
from shopee_order_items as i  
    inner join shopee_order as o on i.ordersn=o.ordersn  
    inner join shopee_cost as c   on c.product_sku=i.variation_sku   
where c.cnname='" . $name . "' and c.comprehensive_cost_us>0 and c.cost_us>0 
  and  o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ") 
    and  o.order_status in $this->status 
  ";
        } else {
            $sql = "select sum(i.credit_card_transaction_fee_us)  as credit_card_transaction_fee_us,sum(i.service_fee_us) as service_fee_us,sum(i.escrow_tax_us) as  escrow_tax_us,sum(i.commission_fee_us) as commission_fee_us 
from shopee_order_items as i  
    inner join shopee_order as o on i.ordersn=o.ordersn  
    inner join shopee_cost as c   on c.product_sku=i.variation_sku   
where c.cnname='" . $name . "' and c.comprehensive_cost_us>0 and c.cost_us>0 
  and  o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid
    and  o.order_status in $this->status 
  ";
        }
        //echo $sql,PHP_EOL;
        $arr = db::select($sql);
        $credit_card_transaction_fee_us = $arr[0]->credit_card_transaction_fee_us;
        $service_fee_us = $arr[0]->service_fee_us;
        $escrow_tax_us = $arr[0]->escrow_tax_us;
        $commission_fee_us = $arr[0]->commission_fee_us;
        $OrderOtherFee = $credit_card_transaction_fee_us + $service_fee_us + $escrow_tax_us + $commission_fee_us;
        return $OrderOtherFee;
    }


    /**
     * sku数量
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function skuNum($sku, $startDateTime, $endDateTime, $shopid)
    {
        //sku数量
        if (is_array($shopid)) {
            $sql = "select count(i.id) as skucount  from shopee_order_items as i inner join shopee_order as o on i.ordersn=o.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ") and i.variation_sku='" . $sku . "' and  o.order_status in $this->status  ";

        } else {
            $sql = "select count(i.id) as skucount  from shopee_order_items as i inner join shopee_order as o on i.ordersn=o.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id= $shopid and i.variation_sku='" . $sku . "' and  o.order_status in $this->status ";
        }
        // echo $sql,PHP_EOL;
        $arr = db::select($sql);
        if (isset($arr[0])) {
            $skucount = $arr[0]->skucount;
        } else {
            $skucount = 0;
        }
        return $skucount;
    }

    /**
     * sku数量
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function nameNumBySku($name, $startDateTime, $endDateTime, $shopid)
    {
        //sku数量
        if (is_array($shopid)) {
            $sql = "select count(i.id) as skucount  from shopee_order_items as i inner join shopee_order as o on i.ordersn=o.ordersn inner join shopee_cost as c on c.product_sku=i.variation_sku    where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ") and c.cnname='" . $name . "' and  o.order_status in $this->status   ";
        } else {
            $sql = "select count(i.id) as skucount  from shopee_order_items as i inner join shopee_order as o on i.ordersn=o.ordersn inner join shopee_cost as c on c.product_sku=i.variation_sku  where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id= $shopid  and c.cnname='" . $name . "' and  o.order_status in $this->status   ";
        }
        // echo $sql,PHP_EOL;
        $arr = db::select($sql);
        if (isset($arr[0])) {
            $skucount = $arr[0]->skucount;
        } else {
            $skucount = 0;
        }
        return $skucount;
    }


    /**
     * 订单列表
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function orders($sku, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select distinct(o.ordersn) from shopee_order as o inner join shopee_order_items as i  on i.ordersn=o.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ") and i.variation_sku='" . $sku . "' and  o.order_status in $this->status ";
        } else {
            $sql = "select distinct(o.ordersn) from shopee_order as o inner join shopee_order_items as i  on i.ordersn=o.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid and i.variation_sku='" . $sku . "' and  o.order_status in $this->status  ";
        }
        $arr = db::select($sql);

        $orders = array();
        foreach ($arr as $item) {
            $orders[] = $item->ordersn;
        }
        return $orders;
    }

    /**
     * 订单列表
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function ordersByName($name, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select distinct(o.ordersn) from shopee_order as o inner join shopee_order_items as i  on i.ordersn=o.ordersn  inner join shopee_cost as c on c.product_sku=i.variation_sku    where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ") and c.cnname='" . $name . "' and  o.order_status in $this->status ";
        } else {
            $sql = "select distinct(o.ordersn) from shopee_order as o inner join shopee_order_items as i  on i.ordersn=o.ordersn  inner join shopee_cost as c on c.product_sku=i.variation_sku  where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid and c.cnname='" . $name . "' and  o.order_status in $this->status  ";
        }

        $arr = db::select($sql);

        $orders = array();
        foreach ($arr as $item) {
            $orders[] = $item->ordersn;
        }
        return $orders;
    }


    /**
     * 获取名称下所有sku
     * @param $name
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     * @param $orders
     */
    public function ordersNameSku($name, $orders)
    {
        $skuArr = array();
        foreach ($orders as $ordersn) {
            $sql = "select variation_sku  from shopee_order_items where ordersn='" . $ordersn . "' ";
            $skuList = db::select($sql);
            // var_dump($skuList);
            foreach ($skuList as $item) {

                $sku = $item->variation_sku;

                if (!in_array($sku, $skuArr)) {
                    $sql = "select *  from shopee_cost  where product_sku='" . $sku . "' and cnname='" . $name . "'  ";
                    $has = db::select($sql);
                    // var_dump($has);
                    if (isset($has[0]->product_sku) && $has[0]->product_sku != '' && $has[0]->cost > 0) {
                        $skuArr[] = $sku;
                    }
                }
            }
        }
        return $skuArr;
    }


    /**
     * 综合费用
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function costOtherFee($sku, $startDateTime, $endDateTime, $shopid, $orders)
    {

        $sql = "select comprehensive_cost_us  from shopee_cost where product_sku='" . $sku . "' ";
        $arr = db::select($sql);
        $skufee = $arr[0]->comprehensive_cost_us;
        $costOtherFee = 0;
        foreach ($orders as $ordersn) {
            $sql = "select count(*) as num from shopee_order_items where ordersn='" . $ordersn . "'";
            $arr = db::select($sql);
            $itemAllNum = $arr[0]->num;

            $sql = "select count(*) as num  from shopee_order_items where ordersn='" . $ordersn . "' and variation_sku='" . $sku . "' ";
            $arr = db::select($sql);
            $skuNum = $arr[0]->num;
            // echo $itemAllNum . '---' . $skuNum, PHP_EOL;
            $costOtherFee = $costOtherFee + (($skuNum / $itemAllNum) * $skufee);
        }
        return $costOtherFee;
    }


    /**
     * 综合费用
     * @param $sku
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function costOtherFeeByName($skus, $startDateTime, $endDateTime, $shopid, $orders)
    {
        $costOtherFee = 0;
        foreach ($skus as $sku) {
            $sql = "select comprehensive_cost_us  from shopee_cost where product_sku='" . $sku . "' ";
            $arr = db::select($sql);
            $skufee = $arr[0]->comprehensive_cost_us;
            foreach ($orders as $ordersn) {
                $sql = "select count(*) as num from shopee_order_items where ordersn='" . $ordersn . "'";
                $arr = db::select($sql);
                $itemAllNum = $arr[0]->num;
                $sql = "select count(*) as num  from shopee_order_items where ordersn='" . $ordersn . "' and variation_sku='" . $sku . "' ";
                $arr = db::select($sql);
                $skuNum = $arr[0]->num;
                // echo $itemAllNum . '---' . $skuNum, PHP_EOL;
                $costOtherFee = $costOtherFee + (($skuNum / $itemAllNum) * $skufee);
            }

        }
        return $costOtherFee;
    }

    /**
     *
     * @param $name
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     * @param $orders
     */
    public function costOtherFeeByNameQuick($name, $startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(i.comprehensive_cost_us) as comprehensive_cost_us  from shopee_order_items as i inner join shopee_order as o on o.ordersn=i.ordersn 
    inner join shopee_cost as c on c.product_sku=i.variation_sku  where   c.cnname='" . $name . "' and c.comprehensive_cost_us>0 and c.cost_us>0 
  and  o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ")
    and  o.order_status in $this->status    ";
        } else {
            $sql = "select sum(i.comprehensive_cost_us) as comprehensive_cost_us  from shopee_order_items as i inner join shopee_order as o on o.ordersn=i.ordersn 
    inner join shopee_cost as c on c.product_sku=i.variation_sku  where   c.cnname='" . $name . "' and c.comprehensive_cost_us>0 and c.cost_us>0 
  and  o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid
    and  o.order_status in $this->status    ";
        }


        $arr = db::select($sql);
        $comprehensive_cost_us = $arr[0]->comprehensive_cost_us;
        return $comprehensive_cost_us;
    }


    /**
     * 毛利率
     * @param $itemId        shopee_item_item的item_id字段
     * @param $startDateTime  开始时间
     * @param $endDateTime   结束时间
     * @param $shopid      店铺
     * @param $priceTotal  销售额
     */
    public function shopeeLirunByName($priceTotal, $name, $startDateTime, $endDateTime, $shopid)
    {
        $startTime = strtotime($startDateTime);
        $startForAdv = date("Y-m-d", $startTime);

        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased) as item_num_all, (c.cost_us+c.comprehensive_cost_us)  as cost_goods_unit,sum(o.credit_card_transaction_fee_us) as card_fee_total
     ,sum(o.service_fee_us) as service_fee_total  
from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   
    inner  join shopee_cost as c   on i.variation_sku=c.product_sku     
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ")  and c.cnname='" . $name . "' and  o.order_status in $this->status     ";
            //  echo $sql,PHP_EOL;
            // exit();
        } else {

        }

        $arr = db::select($sql);
        $row = $arr[0];

        if (isset($row->item_num_all) && isset($row->cost_goods_unit)) {
            $cost_goods = $row->item_num_all * $row->cost_goods_unit;
        } else {
            $cost_goods = 0;
        }


        if (is_array($shopid)) {
            $sql = "select sum(cost) as ad_fee  from  shopee_adv where     `start_date`>='" . $startDateTime . "' and `end_date`<='" . $endDateTime . "'  
        and product_id in (select i.item_id from shopee_order_items as i 
        inner join shopee_order as o   on o.ordersn=i.ordersn 
         inner  join shopee_cost as c   on i.variation_sku=c.product_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ") and  o.order_status in $this->status  and c.cnname='" . $name . "')   ";
            // echo $sql,PHP_EOL;
            // exit();
        } else {
            $sql = "select sum(cost) as ad_fee  from  shopee_adv where     `start_date`>='" . $startDateTime . "' and `end_date`<='" . $endDateTime . "'  
        and product_id in (select i.item_id from shopee_order_items as i 
        inner join shopee_order as o   on o.ordersn=i.ordersn 
         inner  join shopee_cost as c   on i.variation_sku=c.product_sku 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid and  o.order_status in $this->status  and c.cnname='" . $name . "')   ";
        }

        $arr = db::select($sql);
        // $rowAd = $arr[0];
        if (isset($arr[0])) {
            $adFee = $arr[0]->ad_fee;
        } else {
            $adFee = 0;
        }

        $lirun = round(($priceTotal - $cost_goods - $row->card_fee_total - $row->service_fee_total - $adFee), 2);
        return $lirun;
    }


    /**
     * 商品单行数据
     * @param $itemId
     * @return mixed
     */
    public function itemRow($itemId)
    {
        $sql = "select *  from shopee_order_items where item_id=$itemId  limit 1";
        $arr = db::select($sql);
        return $arr;
    }

    /**
     * 商品单行数据
     * @param $itemId
     * @return mixed
     */
    public function itemRowBySku($sku)
    {
        $sql = "select *  from shopee_order_items where variation_sku='" . $sku . "'  limit 1";
        $arr = db::select($sql);
        return $arr;
    }


    /**
     * 获取成本
     * @param $itemSku
     */
    public function getShopeeCost($itemId)
    {
        $sql = "select  c.*  from shopee_cost as c inner join shopee_order_items as i on i.variation_sku=c.product_sku   where i.item_id='" . $itemId . "' limit 1";

        $arr = db::select($sql);
        return $arr;
    }


    /**
     * 获取成本
     * @param $sku
     */
    public function getShopeeCostBysku($sku)
    {
        $sql = "select  *  from shopee_cost    where sku='" . $sku . "' limit 1";
        // echo   $sql,PHP_EOL;
        $arr = db::select($sql);
        return $arr;
    }


    public function weekMostShop()
    {
        $sql = "select *  from shope where id  in  (299)  ";
        $arr = db::select($sql);
        return $arr;
    }


    /**
     * 利润细节
     * @param $itemId        shopee_item_item的item_id字段
     * @param $startDateTime  开始时间
     * @param $endDateTime   结束时间
     * @param $shopid      店铺
     * @param $priceTotal  销售额
     */
    public function lirunDetail($sku, $startDateTime, $endDateTime, $shopid)
    {

        if (is_array($shopid)) {
            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ")   and i.variation_sku='" . $sku . "' and  o.order_status in $this->status   ";
        } else {
            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'       and o.shop_id=$shopid  and i.variation_sku='" . $sku . "' and  o.order_status in $this->status   ";
        }
        $arrPriceTotal = db::select($sql);

        //总金额
        $priceTotal = $arrPriceTotal[0]->pricetotal;

        if (is_array($shopid)) {
            $sql = "select sum(i.variation_quantity_purchased) as item_num_all, (c.cost_us+c.comprehensive_cost_us)  as cost_goods_unit,c.cost_us,c.comprehensive_cost_us
     ,sum(o.credit_card_transaction_fee_us) as card_fee_total,sum(o.service_fee_us) as service_fee_total  
from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   
    inner  join shopee_cost as c   on i.variation_sku=c.product_sku     where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id in (" . implode(",", $shopid) . ")  and i.variation_sku='" . $sku . "' and  o.order_status in $this->status     ";
//            echo $sql, PHP_EOL;
//            echo PHP_EOL;
        } else {
            $sql = "select sum(i.variation_quantity_purchased) as item_num_all, (c.cost_us+c.comprehensive_cost_us)  as cost_goods_unit,c.cost_us,c.comprehensive_cost_us
     ,sum(o.credit_card_transaction_fee_us) as card_fee_total,sum(o.service_fee_us) as service_fee_total  
from  shopee_order_items as i inner  join shopee_order as o on o.ordersn=i.ordersn   
    inner  join shopee_cost as c   on i.variation_sku=c.product_sku     where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'    and o.shop_id= $shopid and i.variation_sku='" . $sku . "' and  o.order_status in $this->status     ";
        }

        $arr = db::select($sql);
        $row = $arr[0];

        if (isset($row->item_num_all) && isset($row->cost_goods_unit)) {
            $cost_goods = $row->item_num_all * $row->cost_goods_unit;
        } else {
            $cost_goods = 0;
        }


        if (is_array($shopid)) {
            $sql = "select sum(cost_us) as ad_fee  from  shopee_adv where     `start_date`>='" . $startDateTime . "' and `end_date`<='" . $endDateTime . "'  
        and product_id in (select i.item_id from shopee_order_items as i inner join shopee_order as o   on o.ordersn=i.ordersn 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'   and o.shop_id in (" . implode(",", $shopid) . ")  and  o.order_status in $this->status  and i.variation_sku='" . $sku . "')   ";

        } else {
            $sql = "select sum(cost_us) as ad_fee  from  shopee_adv where     `start_date`>='" . $startDateTime . "' and `end_date`<='" . $endDateTime . "'  
        and product_id in (select i.item_id from shopee_order_items as i inner join shopee_order as o   on o.ordersn=i.ordersn 
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid and  o.order_status in $this->status  and i.variation_sku='" . $sku . "')   ";

        }

        $arr = db::select($sql);
        // $rowAd = $arr[0];
        if (isset($arr[0])) {
            $adFee = $arr[0]->ad_fee;
        } else {
            $adFee = 0;
        }
        $lirun['total_price'] = $priceTotal;
        $lirun['goods_cost'] = $cost_goods;
        $lirun['card_fee_total'] = $row->card_fee_total;
        $lirun['service_fee_total'] = $row->service_fee_total;
        $lirun['ad_fee'] = $adFee;
        $lirun['cost_goods_unit'] = $row->cost_goods_unit;
        $lirun['item_num_all'] = $row->item_num_all;
        $lirun['cost_us'] = $row->cost_us;
        $lirun['comprehensive_cost_us'] = $row->comprehensive_cost_us;
        return $lirun;
    }


    /**
     * @param $ApiPlanModel  模型
     * @param $param         post,get提交的参数
     */
    public function pageSearch($Model, $param)
    {

//        if (isset($param['sku']) && $param['sku'] != '') {
//            $Model = $Model->where('variation_sku', $param['sku']);
//        }
        if (isset($param['start_date']) && $param['start_date'] != '' && isset($param['sku']) && $param['sku'] != '' && isset($param['shop_id']) && $param['shop_id'] != '') {
//            $startTime = strtotime($param['start_date']);
//            $endTime = strtotime($param['end_date']);
            $startDateTime = $param['start_date'] . ' 00:00:00';//起始时间 日期格式
            $endDateTime = $param['end_date'] . ' 23:59:59'; //截止时间 日期格式
            $Model = Db::table('shopee_order_items as i')
                ->leftJoin('shopee_order as o', 'o.ordersn', '=', 'i.ordersn')
                ->where('i.variation_sku', $param['sku'])
                ->where('o.create_time', '>=', $startDateTime)->where('o.create_time', '<', $endDateTime);

            $arrShop = explode(",", $param['shop_id']);
            if (!empty($arrShop)) {
                $Model = $Model->whereIn('o.shop_id', $arrShop);
            } else {
                $Model = $Model->where('o.shop_id', $param['shop_id']);
            }

            $Model = $Model->whereIn('o.order_status', array('COMPLETED', 'SHIPPED', 'TO_CONFIRM_RECEIVE'));

        }
        return $Model;
    }

    /**
     * 这个店铺这周总共多少单
     * @param $startDateTime
     * @param $endDateTime
     * @param $shopid
     */
    public function shopWeekAllgoodCount($startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select count(i.id)  as allcount from  shopee_order_items as i inner join shopee_order as o where o.ordersn=i.ordersn and o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id in (" . implode(",", $shopid) . ") and  o.order_status in $this->status    ";
        } else {
            $sql = "select count(i.id)  as allcount from  shopee_order_items as i inner join shopee_order as o where o.ordersn=i.ordersn and o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     and o.shop_id=$shopid and  o.order_status in $this->status    ";
        }
        $arr = db::select($sql);
        return $arr[0]->allcount;
    }

    public function shopWeekAllAdFee($startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(cost_us) as all_ad_fee  from  shopee_adv where  `start_date`>='" . $startDateTime . "' and `end_date`<='" . $endDateTime . "'   and shop_id in (" . implode(",", $shopid) . ")  ";

        } else {
            $sql = "select sum(cost_us) as all_ad_fee   from  shopee_adv  where `start_date`>='" . $startDateTime . "' and `end_date`<='" . $endDateTime . "'   and shop_id=$shopid";
        }
        //echo $sql,PHP_EOL;
        $arr = db::select($sql);
        return $arr[0]->all_ad_fee;
    }

    /**
     * 一周总销售额
     */
    public function shopWeekSaleTotal($startDateTime, $endDateTime, $shopid)
    {
        if (is_array($shopid)) {
            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
   
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     
  and o.shop_id in (" . implode(",", $shopid) . ")    and  o.order_status in $this->status   ";
        } else {
            $sql = "select sum(CASE WHEN i.variation_discounted_price_us = 0.00 THEN i.variation_original_price_us* i.variation_quantity_purchased ELSE i.variation_discounted_price_us* i.variation_quantity_purchased end ) as pricetotal from  shopee_order_items as i 
    inner  join shopee_order as o on o.ordersn=i.ordersn 
   
where o.create_time>='" . $startDateTime . "' and o.create_time<'" . $endDateTime . "'     
  and o.shop_id=$shopid    and  o.order_status in $this->status   ";
        }
        $arr = db::select($sql);
        return $arr[0]->pricetotal;

    }


}
